Is a multi-cell VLOOKUP possible?

[jardenp]

This came up from trying to answer another question in this forum:

Is it possible to have a VLOOKUP or something like it that matches the contents of two cells and returns that of a third? For example:


A - B - C

1 - a - i
2 - b - ii
1 - c - iii
2 - d - iv

The lookup for "1" and "a" would return "i"
The lookup for "1" and "c" would return "iii"
etc.

I know you could do this by concatenating columns A and B and then running a normal VLOOKUP on the combined values, but is it possible to do this without concatenation?

Thanks!

 

[hiker95]

jardenp,


Something like this?

Excel Workbook
	A	B	C
1	1	a	i
2	2	b	ii
3	1	c	iii
4	2	d	iv
5
Sheet1

The formula in cell C1 copied down:


=LOOKUP(B1,{"a","b","c","d"},{"i","ii","iii","iv"})





Can you show us the table for combinations of 2,a, etc?

 

[Jon von der Heyden]

Perhaps;

=LOOKUP(2,1/((A1:A100=1)*(B1:B100="a")),C1:C100)

The criteria (in red) can also point to a cell reference instead.

If you have the combinations of criteria repeated then this will return the value from the last occurence, whilst conventional single criterion VLOOKUP would return from the first match.

 

[jardenp]

Hiker - I should have been more specific and said that the cell contents were just sample/placeholder values. Sorry. And that this would be used for large data sets with values not pre-known. Therefore, a formula that requires entry of each value wouldn't work. Thanks any way though. I appreciate the reply.

Jon - Perfect. And elegant. Thanks. I changed the ranges from A1:A100 to just A:A, etc. to make it not limited in range and it still works perfectly. I don't understand how it works, but it does, so thanks! There shouldn't be any repeated combinations in my data sets, so the first/last distinction shouldn't matter, but I appreciate the heads up.

 

[Jon von der Heyden]

Glad you got it working.

I changed the ranges from A1:A100 to just A:A, etc. to make it not limited in range and it still works perfectly.

You must be using XL07 or more recent. Whole columns are not supported in earlier versions, and in fact I would recommend that you avoid whole columns. If table size is going to be an issue then rather set-up some dynamic named ranges.

Height: =MATCH(9.99999999999999E+307,SheetName!$A:$A)
ColA: =INDEX(SheetName$A:$A,Height)
ColB: =INDEX(SheetName$B:$B,Height)
ColC: =INDEX(SheetName$C:$C,Height)

Then your formula:
=LOOKUP(2,1/((ColA=1)*(ColB="a")),ColC)

Although I recommend that you use more meaningful names than ColA,ColB and ColC...

 

[jardenp]

Jon - I'm using 2007. Why not whole columns? Because it's not backwards compatible? That is, if this won't be used on pre-2007 Excel, why not? Just curious.

***********
An update for any future browsers looking a solution to this type of problem:

Jon von der Heyden's solution is scalable. It can be used to return a value with three, four, etc. columns in the reference range. The parts in {} brackets require changing to your ranges.

=LOOKUP({# of columns in the reference range},1/(({ref col 1}={first cell to look up})*({ref col 2}={second cell to look up})*{repeat the (range=cell) groups as needed),{range of value to return})

(that's gruesomely confusing upon review, but maybe it will make sense)

 

[Jon von der Heyden]

Hi

I generally recommend that you don't use whole columns for any formula that processes arrays, because the arrays will be oversized and slow down the calculation. That said I'm not actually sure if that applies in this context, or if XL07 handles this better... So no real problem if you are using XL07, just a recommendation made on my part more as a precaution.

With respect to your evaluation of the formula, you're close but not exactly spot on. The lookup value will always be 2. If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

The array we use, 1/((ColA=1)*(ColB="a")), will initially evaluate each condition statement first (e.g. ColA=1) and return an array of boolean results based on wether or not the condition is met for each cell in the range (returns e.g: {TRUE,FALSE,FALSE,FALSE...}. The two arrays are then mutlipled against each other, which also acts as coersion (multiple a boolean by another will yield 1 if both statements are TRUE, else 0). This then forms a single array {1,0,0,0,0,0...}. Each value in the array is divided by 1, which then yields an array of only 1's and #DIV/0!'s, e.g. {1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!...}.

Lookup(2,TheNewArrayDescribedAbove,TheRangeToReturnValueFrom) will return the result from the last 1 found (in my example the only 1 and also happens to be the first array item), and then return the value from the range in equivalent position.

I'm not so hot on explaining these things but you can search for this method posted by Aladin and Jonmo1. I know both have gone to some length explaining this method.

 

[jardenp]

Jon -

Thanks for the clarifications. I think I follow what you're saying. The part about not using whole columns may explain why some of my macros make worksheets thousands of rows long. There's no data in the extra thousands, but the scroll bar on the right side of the screen will become a sliver, so you can the sheet is registering thousands of rows. Also greatly increases the macro run time.

Thanks again, you've been a great help.

 

[MassSpecGuru]

This came up from trying to answer another question in this forum:

Is it possible to have a VLOOKUP or something like it that matches the contents of two cells and returns that of a third? For example:


A - B - C

1 - a - i
2 - b - ii
1 - c - iii
2 - d - iv

The lookup for "1" and "a" would return "i"
The lookup for "1" and "c" would return "iii"
etc.

I know you could do this by concatenating columns A and B and then running a normal VLOOKUP on the combined values, but is it possible to do this without concatenation?

Thanks!

Does this help?

http://blogs.techrepublic.com.com/msoffice/?p=3306

 

[jardenp]

MassSpecGuru: Mr. von der Heyden kindly provided me with a solution, though I'm glad I read the article you linked. Thanks.