CODEWITHSUNDEEP
google-sheetsmatchgoogle-sheets-formulaarray-formulasgs-vlookup
C.Jay
C.Jay

Reputation: 15

Dynamic Array vlookup formula (Hlookup in a vlookup ??)

I'm stuck with a complex array vlookup formula. Explanation:

A simple goal, when a worker has a worksite write in his own line, import other columns via Vlookup. ( Easy )

But I'm trying to do something more: every worker have different type job I want that the vlookup import a different column for every worker, base on the job type.

So, it's like the return columns have to be different for every worker ... (sound strange)

it's like a vlookup in a vlookup or hlookup in a vlookup?

Any idea how to make this work?
here, a link to the example sheet:

https://docs.google.com/spreadsheets/d/1SY27Hw_Ck24RBJmh5n8hcyt3TrxTm1YPGDKU9DqA7xE/edit?usp=sharing

image exemple

Upvotes: 1

Views: 668

Answers (2)

Alessandro
Alessandro

Reputation: 2998

Instead of using vlookup the second time it's better to use the combination of Index and Match functions. Like this you can get the offset index you need on the first lookup.

so being this my Sheet2:

+---+----+----+
|   | 1  | 2  |
|---|----|----|
| a | aa | bb |

in the first sheet I will have

+---+---+----------------------------------------------------------------+
| a | 2 | `=VLOOKUP(B1,Sheet2!A1:Z100,MATCH(C1,Sheet2!B1:Z1) + 1)   //bb`|

Upvotes: 0

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, DB_Localisation!A2:AQ, 
 MATCH(E2:E, DB_Localisation!1:1, 0), 0)))

0

spreadsheet demo

Upvotes: 1

Related Questions