Increment based on different table-CodePudding

I have a list of items for which I need to fetch various info for and make a series of calculations in a different table.

So I have two tables, one with the list of items, and another "info table" with the various calculations, currently for the first item only.

But I need to have info tables for each and every item in the list, so instead of manually updating the coordinates what I'd like to do is something like copy and paste the info table for the first item and have the top fields update based on an increment of the item list.

Is there an easy way to do this in google sheets?

CodePudding user response：

if you need formula in B4 use:

=INDEX(IFNA(VLOOKUP(B4:B; D:E; 2; )))

if you want formula in E6 use:

=INDEX(IFNA(VLOOKUP(D6; A:B; 2; )))

update:

use in column D as many times as you need

=ARRAYFORMULA(IF(ROW()=6, A$4:B$4, INDIRECT(
 ADDRESS(MAX(IFNA(MATCH(INDIRECT("D6:D"&ROW()-1), A:A, 0))) 1, 1, 4)&":"&
 ADDRESS(MAX(IFNA(MATCH(INDIRECT("D6:D"&ROW()-1), A:A, 0))) 1, 2, 4))))