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.

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).

Attached Images

Hetty.jpg (63.5 KB, 5 views)