Vlookup on multiple tables

[Dalex]

Hello,

I'm attemting to perform a VLOOKUP on multiple tables, but am having challenges. First of all I don't think it is possible to perform a VLOOKUP on multiple tables so I thought it practical to copy the tables's data into one large table, then perform the VLOOKUP from that that one "master table". Unfortunately this does not work UNLESS I put the "master table" in some kind of ascending order. I prefer not to use macros...only formulas.

Here is the code I am currently working with:

=IF((C67>0),VLOOKUP(C67,Entry!$AI$4:AI59,2),"")

Any suggestion will be much appreciated.

Thanks.

Dalex

 

[logansdad]

I have a suggestion to try that I have used a few times. For Ease of use I put started with Table 1, then put the rest of the tables in line to the right of Table1. I used Table2,Table3,Table4, etc. I defined 2 cells, one that I am using what I am looking up, and another Providernumber if you will.
and then I used the Choose Command. So I hads a Formula like
=VLOOKUP(datatolookup,CHOOSE(providernum,table1,table2,table3),2)

So Data to look up is what we are looking up, The CHOOSE command tells Excel which Table to Use to do the Vlookup on, and then of course the colume to use. I use a Macro to Fill in providernumber, and 1 simply gets table1,2 Table2, etc. I do beleive that Excel allows you do this for up to 27 Tables
H T H
:wink:

 

[buksy]

hy there.
can someone help me with a Vlookup formula?i have a table with more than 65536 rows,so i split it in to two sheets.(sheet 2,sheet 3).What i want the formula to do is:if i put a number in sheet 1 A1,in B1 to put the value next to the number asocieted from sheet 2 or sheet3(where it finds it.).thanks

 

[Aladin Akyurek]

hy there.
can someone help me with a Vlookup formula?i have a table with more than 65536 rows,so i split it in to two sheets.(sheet 2,sheet 3).What i want the formula to do is:if i put a number in sheet 1 A1,in B1 to put the value next to the number asocieted from sheet 2 or sheet3(where it finds it.).thanks

Is the result that is expected a number?

 

[buksy]

thanks for the quick response.here is a selection of the data were i want to use the formula.Question mark is werei want the result to be.

 

[Aladin Akyurek]

thanks for the quick response.here is a selection of the data were i want to use the formula.Question mark is werei want the result to be.
[...]

No need for such images. Really.

Sheet1

B2:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},
  VLOOKUP(A2,Sheet2!A:B,2,0),VLOOKUP(A2,Sheet3!A:B,2,0)))

If the data on Sheet2 and Sheet3 are sorted in ascending order...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},
  IF(VLOOKUP(A2,Sheet2!A:A,1,1)=A2,VLOOKUP(A2,Sheet2!A:B,2,1)),
  IF(VLOOKUP(A2,Sheet3!A:A,1,1)=A2,VLOOKUP(A2,Sheet3!A:B,2,1))))

If the sorting condition holds, the 2nd formula would execute way faster.

 

[buksy]

sorry for the image.my english is not so great and i wanted to make sure you understand me.
i tried both formulas but in every case i recive an message that says something like "the formula has an eror".If I click ok ,9.99999999999 is selected for modifications.

 

[buksy]

Since there are only 2 colums on each tables I decided to combine Sheet 2 an Sheet 3 into „CODURI”,but with 4 colums(A-NR.,B-GRUP,C-NR.,D-GRUP).And to get the result I used this formula:


=LOOKUP(9,99999999999999E+307;CHOSE({1;2;3};0;VLOOKUP(A15702;CODURI!A2:B65371;2;FALSE);VLOOKUP(A15702;CODURI!D2:E65;2;FALSE)))

But now it gives me #NAME?



The value 903 is corect from the second formula of Vlookup,but the final result is an eror because of he firs Vlookup.

 

[Aladin Akyurek]

Since there are only 2 colums on each tables I decided to combine Sheet 2 an Sheet 3 into „CODURI”,but with 4 colums(A-NR.,B-GRUP,C-NR.,D-GRUP).And to get the result I used this formula:


=LOOKUP(9,99999999999999E+307;CHOSE({1;2;3};0;VLOOKUP(A15702;CODURI!A2:B65371;2;FALSE);VLOOKUP(A15702;CODURI!D2:E65;2;FALSE)))

But now it gives me #NAME?



The value 903 is corect from the second formula of Vlookup,but the final result is an eror because of he firs Vlookup.

You have a typo regarding the CHOOSE function...

=LOOKUP(9,99999999999999E+307;CHOOSE({1;2;3};
  0;
  VLOOKUP(A15702;CODURI!A2:B65371;2;0);
  VLOOKUP(A15702;CODURI!D2:E65;2;0)))

 

[buksy]

thaks alot for the help.I succeded with the first formula.i belive that the problem was ","because after i replaced it with ";" it started to work.the single problem is that(as you said)is kinda slow,so tomorow i wil try the other formula that you gaved me.

 

[buksy]

Now i have another question for you and I write you here because I didn't find a thread about my subject.
Is there a formula that can help me copy an entire row to another sheet if a condition(a word or number) on that row is accomplished?
Thank you.