Look for a value in multiple column and vlookup that value on another sheet

[golden2282]

I want to look for a value which may exist in the range G2:I2. When a value is found, I want to find the corresponding value in column B and return the value found in column C.

I started by thinking i could just =vlookup(G2,B:C,2,0)+vlookup(H2,B:C,2,0)...etc.

Then I tried figuring out some index match combination and i can return which column the value is in. I see that being useful in combination with another formula but I just can't quite get it.

Excel Test.xlsx

[zbor]

I can't figure how you get 75 for example

Please Login or Register  to view this content.

[golden2282]

For the cell K3, the job number is 400 (G3). The job 400 in column B is in B3 and the value for "Dept 1" is 0.

For the cell L4, the job number is 200 (H4). The job 400 in column B is in B5 and the value for "Dept 2" is 150.

Excel Test Pic.JPG

[zbor]

here, try this:

Please Login or Register  to view this content.

[golden2282]

That worked! I just had to Google the LOOKUP(99^99) function to see what the heck you did.