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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
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 ! ;)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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
Back
Top