Vlookup Twist: retrieve one value based on Multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mellowe - 18 Jan 2006 16:16 GMT

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

Reply to this Message

Pete_UK - 19 Jan 2006 00:29 GMT

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

Reply to this Message

mellowe - 19 Jan 2006 10:47 GMT

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

Reply to this Message

Pete_UK - 19 Jan 2006 11:18 GMT

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

Reply to this Message

mellowe - 19 Jan 2006 12:46 GMT

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

Reply to this Message

Pete_UK - 19 Jan 2006 12:50 GMT

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

Reply to this Message