[Hetty]

Hi, I am trying to write a formula for work and keep coming up with errors.

Below is an example of what i'm trying to accomplish. I have 2 sheets and am trying to vlookup from sheet1 to sheet2 (vlookup(b3,sheet1c:d,2,false). My problem is i have identical data on sheet 1 with the difference in column b. I tried to combine if and vlookup but to no avail. Is there a way to say if sheet2 column a = sheet1 columb b then look for b3 and return the value in column d?
I sure hope that makes sense.

a b c d sheet1
3 john 1234 1.255
4 john 2345 1.255
5 john 3456 1.255
6 john 4567 1.255
7 john 5678 1.255
8 jane 1234 2.255
9 jane 2345 2.255
10 jane 3456 2.255
11 jane 4567 2.255
12 jane 5678 2.255

sheet2
john 3456
jane 2345



Thank you for any help you can give.:4-dontkno

 

[David M58]

As an example, if Sheet1 and Sheet2 contain the data shown in the attached picture, and you wanted to have Excel look at the data in columns A and B on Sheet2, find the matching values in columns B and C on Sheet1, return the corresponding value in column D on Sheet1, and place that result in column C on Sheet2, you could enter an array formula in C1 on Sheet2 such as:

{=INDEX(Sheet1!$D$1:$D$10,MATCH(1,(Sheet1!$B$1:$B$10=$A1)*(Sheet1!$C$1:$C$10=$B1),0))}

Remember to use Ctrl-Shift-Enter to enter array formulas (you do not type the { and } characters).