Vlookup..until

Bee1234

New Member
Joined
Jan 12, 2018
Messages
5
Dear All,

I am glad to be here, and hope you can answer my question.

I need to vlookup some items..but! If it is zero or error go to an other table if again go to a third if still not get a value from given cell.

I would really appreciate if any of you could help me with this issue.

Thank you
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

Perhaps something like this . . .

=IFERROR(VLOOKUP(A1,B1:C10,2,FALSE),VLOOKUP(A1,D1:E10,2,FALSE))

This attempts to perform the vlookup in B1:C10 first, and if that fails, try it in D1:E10.

You can expand on this to make several different attempts if you like.

Another option might be to merge all your lookup tables into one single table.
 

Bee1234

New Member
Joined
Jan 12, 2018
Messages
5
Hi, welcome to the board.

Perhaps something like this . . .

=IFERROR(VLOOKUP(A1,B1:C10,2,FALSE),VLOOKUP(A1,D1:E10,2,FALSE))

This attempts to perform the vlookup in B1:C10 first, and if that fails, try it in D1:E10.

You can expand on this to make several different attempts if you like.

Another option might be to merge all your lookup tables into one single table.[/QUOTE





Thank you Gerald!


Its almost perfect;) the only remaining trick, that vlookup if error OR!zero.
 

Bee1234

New Member
Joined
Jan 12, 2018
Messages
5

ADVERTISEMENT

Hello

I mean if the value the vlookups found is zero, so o have 2 criteria; 1 error message 2 valie vlookup founds =o thank you!
 

Bee1234

New Member
Joined
Jan 12, 2018
Messages
5

ADVERTISEMENT

Sorry misspelling;so vlookup in other table if error message Or! zero value
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hmm . . .

If you really want to do this, try this . . .

=IF(OR(VLOOKUP(A1,B1:C10,2,FALSE)=0,ISNA(VLOOKUP(A1,B1:C10,2,FALSE))),VLOOKUP(A1,D1:E10,2,FALSE),VLOOKUP(A1,B1:C10,2,FALSE))

or a slightly different and shorter version . . .

=IF(ISERROR(0/VLOOKUP(A1,B1:C10,2,FALSE)),VLOOKUP(A1,D1:E10,2,FALSE),VLOOKUP(A1,B1:C10,2,FALSE))

Note - this shorter version will ONLY work if the lookup value is numeric.
If your lookup value is text, it won't work properly.


As you can see, both are a bit messier than the original.

You can extend both of these out to testing 4 ranges if you like, but the final version will be a good bit more complicated.

As I said before, consider merging your lookup tables into a single table.
 
Last edited:

Bee1234

New Member
Joined
Jan 12, 2018
Messages
5
Hmm . . .

If you really want to do this, try this . . .

=IF(OR(VLOOKUP(A1,B1:C10,2,FALSE)=0,ISNA(VLOOKUP(A1,B1:C10,2,FALSE))),VLOOKUP(A1,D1:E10,2,FALSE),VLOOKUP(A1,B1:C10,2,FALSE))

or a slightly different and shorter version . . .

=IF(ISERROR(0/VLOOKUP(A1,B1:C10,2,FALSE)),VLOOKUP(A1,D1:E10,2,FALSE),VLOOKUP(A1,B1:C10,2,FALSE))

Note - this shorter version will ONLY work if the lookup value is numeric.
If your lookup value is text, it won't work properly.


As you can see, both are a bit messier than the original.

You can extend both of these out to testing 4 ranges if you like, but the final version will be a good bit more complicated.

As I said before, consider merging your lookup tables into a single table.




Thank you !!! I know the other was more "elegant" and this seems to be "messy" but perfect ! thank you for your time and help ! ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,472
Messages
5,601,854
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top