Vlookup Twist: retrieve one value based on Multiple criteria

[mellowe]

Hi All

Please Please help!!
I have two spreadsheets each with one data ranges (same range -
B7:O200), I need a lookup formula that copies the value in column O7 on
s/sheet1 to O7 in s/sheet2 if the values in s/sheet1 columns B7,C7 and
E7 match those in s/sheet2

The twist is this as this is a range the values in s/sheet2 could be in
different rows to that in s/sheet1 so if match was found a "0" would be
entered in col O :

Sheet1:
B C D E .... O
7 DRF09 GBP 12340 12234.90 QWE
8 PLF99 EUR 1340 2234.80 RXP
9 BNF98 GBP 10900 23939.00 PLM

Sheet2:
B C D E .... O
7 PLF99 EUR 1340 2234.80 RXP
8 MNF22 GBP 11236 1450.22 0
9 DRF09 GBP 12340 12234.90 QWE

I have tried vlookup, index, match you name it but cant find a lookup
for checking more than one criteria. PLease help!!! thnx

 

[Pete_UK]

In s/sheet1 make use of two helper columns, P and Q. In P7 enter the
formula:

=B7&C7&E7

and copy down to P200. In Q7 enter the formula:

=O7

and copy down to Q200. I assume the two sheets are in the same file -
in P7 of Sheet2 enter the formula:

=B7&C7&E7

and copy down to P200. In O7 of Sheet2 enter the formula:

=IF(ISNA(VLOOKUP(P7,Sheet1!P$7:Q$200,2,0)),0,VLOOKUP(P7,Sheet1!P$7:Q$200,2,0))

and copy down to O200. This will give you what you want.

You can fix the values in Sheet2 by highlighting O7 to O200, click
<copy> then Edit | Paste Special | Values | OK then <esc>, and then you
can delete column P and columns P and Q in Sheet1.

Hope this helps.

Pete

 

[mellowe]

Brilliant, Brilliant, Brilliant!!!! Thankyou very much worked
perfect!!! - Exactly what i needed!

 

[Pete_UK]

Thanks for the feedback.

I did think you might need to change the formula entered into P7 to:

=B7&C7&ROUND(E7,2),

but if it has worked then fine!

Pete

 

[mellowe]

nope done the job as it is ... thank you so much again for taking the
time to look at this for me!!

 

[Pete_UK]

Thank you, also, for describing your problem so well - I was able to
give you the solution with direct references to the cells and ranges
which you used, so you could more easily relate it to your sheets.

Pete