Vlookup, if, or ?

Chunky_22 · Jun 28, 2018

Hi

(I really hope Im explaining this sensibly...)

On sheet1 I have a list of part numbers, approx 20,000 rows long. Some of the Part numbers have a "W10" added to the end:

aqpPqd12 $25
aqpPqd14 $30
aqpSqd15 <blank cell>
aqpSqd15W10 $70

On sheet2 I have a shortened list of a few hundred part numbers, and I am using a basic VLOOKUP to pull the price for each part number =VLOOKUP(A2,Sheet1!A1:D19,2,FALSE). Some prices are blank and that's fine.

However, my lists on Sheet2 do not include the W10 options. Therefore my formula wouldnt pick up the price for aqpSqd15W10 from the list above because the formula is looking for aqpSqd15.

I need to write a formula that says if the result of the VLOOKUP is a blank cell (ISNA? ISERROR?) then do the VLOOKUP again but add "W10" to the code you're looking for. The result of that VLOOKUP could also be a blank cell, but that's fine.

Does anyone know a way I can deal with this? I have so many workbooks to process, a solution could save me days of manual editing.
Thanks

Yongle · Jun 28, 2018

hi

either of these should work for you

=IFNA(VLOOKUP(A2,Sheet1!A1:D19,2,FALSE),VLOOKUP(A2&"W10",Sheet1!A1:D19,2,FALSE))

=IFERROR(VLOOKUP(A2,Sheet1!A1:D19,2,FALSE),VLOOKUP(A2&"W10",Sheet1!A1:D19,2,FALSE))

jtakw · Jun 28, 2018

Hi,

Since VLOOKUP will only return the First match, if in Sheet1, you have data Just like you posted (with Both aqpSqd15 & aqpSqd15W10 in the list), whether you Lookup "aqpSqd15" or "aqpSqd15W10", it will Always return the Price that is First found from D1 downwards...

With that said, you can use a wildcard:

=VLOOKUP(A2&"*",Sheet1!A1:D19,2,0)

Yongle · Jun 28, 2018

IMO the wildcard suggestion is too risky

As stated by @jtakw - if the items in sheet1 are not sequenced correctly, the formula will return the first wildcard match
eg if aqpSqd15W10 is found first, the wildcard formula will never return the correct match for aqpSqd15

RasGhul · Jun 28, 2018

Hi Chunky,

Insert a helper column as column A to Sheet1 and use;

Code:

=IF(ISERROR(SEARCH("W10",B1)),B1,LEFT(B1,SEARCH("w10",B1)-1))

Your Sheet2 Lookup will now be;

Code:

[COLOR=#333333]=VLOOKUP(A2,Sheet1!A1:E19,3,FALSE)[/COLOR]

RasGhul · Jun 28, 2018

Oops I forgot about duplicates

jtakw · Jun 28, 2018

Yongle said:
IMO the wildcard suggestion is too risky
As stated by @jtakw - if the items in sheet1 are not sequenced correctly, the formula will return the first wildcard match
eg if aqpSqd15W10 is found first, the wildcard formula will never return the correct match for aqpSqd15

I disagree that using the wildcard is "risky

", that's Exactly why I said what I said in my post #3 (regarding 1st match). And besides OP, who's to say what is the "Correct" match?

See sample below:

Book1

A

B

C

D

E

F

G

1

My formula from Post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL]

Yongle's 1st formula from Post #2

Yongle's 2nd formula from Post #2

2

aqpSqd15

0

aqpPqd12

25

3

aqpPqd14

30

4

aqpSqd15

5

aqpSqd15W10

70

6

7

aqpSqd15

0

70

aqpPqd12

25

8

aqpPqd14

30

9

aqpSqd15W10

10

aqpSqd15

70

11

12

aqpSqd15

70

0

aqpPqd12

25

13

aqpPqd14

30

14

aqpSqd15W10

70

15

aqpSqd15

Sheet109

Cell Formulas
Range	Formula
C2	=VLOOKUP(A2&"*",F2:G5,2,0)
C7	=VLOOKUP(A7&"*",F7:G10,2,0)
C12	=VLOOKUP(A12&"*",F12:G15,2,0)
D2	=IFNA(VLOOKUP(A2,F2:G5,2,FALSE),VLOOKUP(A2&"W10",F2:F5,2,FALSE))
D7	=IFNA(VLOOKUP(A7,F7:G10,2,FALSE),VLOOKUP(A7&"W10",F7:F10,2,FALSE))
D12	=IFNA(VLOOKUP(A12,F12:G15,2,FALSE),VLOOKUP(A12&"W10",F12:F15,2,FALSE))
E2	=IFERROR(VLOOKUP(A2,F2:G5,2,FALSE),VLOOKUP(A2&"W10",F2:G5,2,FALSE))
E7	=IFERROR(VLOOKUP(A7,F7:G10,2,FALSE),VLOOKUP(A7&"W10",F7:G10,2,FALSE))
E12	=IFERROR(VLOOKUP(A12,F12:G15,2,FALSE),VLOOKUP(A12&"W10",F12:G15,2,FALSE))

TotallyConfused · Jun 28, 2018

Hello Chunky_22

This has been an interesting challenge you posed for us, one that I couldn’t resist working on. I see there have been a few other possible solutions offered, some with built in problems. I think, at least I hope, I have come up with a solution that should solve your problem. It doesn’t seem to matter whether the basic part number or the one with the ‘W10’ extension comes first.
1) If the basic part number has a blank cell (zero balance) then I show the value for the ‘W10’ number.
2) If both versions have a dollar value (which judging from your original post I don’t think would happen) my formula will show the dollar value for the basic part number.
3) If both versions exist on Sheet 1 and both have a zero-dollar balance, then my formula will show a zero.

Code:

  =IF(VLOOKUP(A2,Sheet1!A1:D19,2,FALSE)="",VLOOKUP(A2&"W10",Sheet1!A1:D19,2,FALSE),VLOOKUP(A2,Sheet1!A1:D19,2,FALSE))

Yongle · Jun 28, 2018

Hi Chunky_22
Your formula includes lookup range A1:D19 which should probably be absolute reference $A$1:$D19 (to prevent Excel coping it down as A2:D20 etc..)

So here is my earlier suggestion in post#2 amended to reflect that

=IFNA(VLOOKUP(A2,Sheet1!$A$1:$D19,2,FALSE),VLOOKUP(A2&"W10",Sheet1!$A$1:$D19,2,FALSE))

=IFERROR(VLOOKUP(A2,Sheet1!$A$1:$D19,2,FALSE),VLOOKUP(A2&"W10",Sheet1!$A$1:$D19,2,FALSE))

(I prefer to use 0 (zero) rather than FALSE as the 4th argument in VLOOKUP -the results are identical so its purely a personal preference thing

)

Vlookup, if, or ?

Chunky_22

Board Regular

Excel Facts

Yongle

Well-known Member

jtakw

Well-known Member

Yongle

Well-known Member

RasGhul

Well-known Member

RasGhul

Well-known Member

jtakw

Well-known Member

TotallyConfused

Board Regular

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

Vlookup, if, or ?

Board Regular

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock