Status
Not open for further replies.
1 - 2 of 2 Posts

#### Hetty

· Registered
Joined
·
1 Posts
Discussion Starter · ·
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

#### David M58

· Registered
Joined
·
160 Posts
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).

#### Attachments

• 63.5 KB Views: 32
1 - 2 of 2 Posts
Status
Not open for further replies.