Multi Column vlookup

[Chillifire]

I have a vlookup formula, which works without a problem, but my lookup data is too big to fit in one column and have to put the overflow data in another lot of columns. Is there a way to allow the vlookup to search the first lot of data and if it not there search the next lot of column data. I am using excel 2003. I have got a thought maybe match might work?

 

[Hui]

Chillifire


Firstly, Welcome to the Chandoo.org Forums


You can use the following in conjunction with an Index or Offset to do something


Assuming what you want to find is in E1


To find the Row where your data is

=SUMPRODUCT((A:C=E1)*(ROW(A:C)))


To find the Column where your data is

=SUMPRODUCT((A:C=E1)*(COLUMN(A:C)))


Adjust to suit

 

[mycko]

you can try to use isna() with vlookup

if your data is in 3 column of data (A:B,C:D and E:F) and you search for G1

=IF(ISNA(VLOOKUP(G1,A:B,2,FALSE)),IF(ISNA(VLOOKUP(G1,C:D,2,FALSE)),IF(ISNA(VLOOKUP(G1,E:F,2,FALSE)),"not exist",VLOOKUP(G1,E:F,2,FALSE)),VLOOKUP(G1,C:D,2,FALSE)),VLOOKUP(G1,A:B,2,FALSE))

 

[Chillifire]

Thank you for the reply, looks interesting, will try to apply to my problem and let you know the result.

 

[Guest]

Hi,


Rather than lots of Vlookups doubling up in the IsNA sollution, could you not nest iferrors?


=iferror(VLOOKUP(G1,A:B,2,FALSE),iferror(VLOOKUP(G1,C:D,2,FALSE),iferror(VLOOKUP(G1,E:F,2,FALSE),"not exist")))


Not much difference in this example, but should make it much easier to follow if your data is much larger.


As an asside, if your data is this big, is this the right way to structure it? Where are you getting your data from? & What are you trying to do with it?


If it is in a database can you create a data connection and use that to populate a pivot table? You could then use a slicer or pivot filter to get the data you need.


Just an idea, hope it helps


M