CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
NicCio
NicCio

Reputation: 41

How would I copy a cell based on multiple cell values

Morning all, I have a question I'm hoping someone can help me with a formula for. I'm trying to return a value from Sheet2 into Sheet1 based on two different criteria. For example:

Sheet1:

Lvl Amount ID
1A $ 001
2B $ 002
2C $ 003
1B $ 001

Sheet2:

Lvl 001 002 003
1A $300 $275 $250
1B $200 $175 $150
1C $100 $75 $50
2A $350 $325 $315
2B $250 $225 $210
2C $150 $125 $110

So on Sheet1, if the first column value matches the Sheet2 first column value, then return the value in that row for the correct ID. The completed would be:

Sheet1 (completed):

Lvl Amount ID
1A $300 001
2B $225 002
2C $110 003
1B $200 001

I'm stumped on how to do a multiple lookup like this though and on what formula would go in Sheet1 in the Amount column. Also, I can't use VBA as it is blocked by my company.

Any help would be appreciated!

Upvotes: 0

Views: 362

Answers (1)

Bobort
Bobort

Reputation: 3218

I would use HLOOKUP and MATCH.

=HLOOKUP(C2,Sheet2!$A:$D,MATCH(A2,Sheet2!$A:$A,0))

MATCH will return the row index of the Lvl that you are looking for. HLOOKUP needs the row index to look in, and it will use the ID number as C2 to find that in the table columns.

Upvotes: 1

Related Questions