# Vlookup Twist: retrieve one value based on Multiple criteria

1. ## Vlookup Twist: retrieve one value based on Multiple criteria

Hi All

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

2. ## Re: Vlookup Twist: retrieve one value based on Multiple criteria

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

3. ## Re: Vlookup Twist: retrieve one value based on Multiple criteria

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

4. ## Re: Vlookup Twist: retrieve one value based on Multiple criteria

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

5. ## Re: Vlookup Twist: retrieve one value based on Multiple criteria

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

6. ## Re: Vlookup Twist: retrieve one value based on Multiple criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1