Excel Question #46

Kevy Baby · 12-10-2010, 10:32 PM

Alright, next question on lookups...

I have fallen in love with VLookups and I use them frequently(although for some reason I have yet to need to use an HLookup function). I am such a geek.

I am sure this is another simple one, but my searches haven't provided the results I need. Basically, I need to find the value in an array when value A matches the header across the top and value B matches the list down the side.

For example, if I had three column headers; apple, banana, and orange and three row headers; Ralph, Fred, and George. For simplicity's sake, lets say the values (left to right by row) are 1-9 (apple/Ralph is 1, banana/Ralph is 2, orange/Fred is 8, etc.)

How do I look up the value based on TWO values - so that the look up knows that when value A is (for example) orange and value B is Fred, that the result is "6"?

ETA: for what I needed tonight, I was able to cheat by using a VLookup since the column header info was just the numbers 1-8 (UPS Zones). By using this formula:

=VLOOKUP(J3,'[Lookup table 121010.xlsx]Sheet4'!$A$35:$I$42,H3+1,FALSE)

In this case, you will see I used the value in H3 (Zone) and added one to it. Since the first column of the array was the value that VLookup was looking for a match on, the second column would be Zone 1, the third Zone 2 and so on. By adding 1 to the value of the zone, it made the formula look in the correct column. I just couldn't figure out how to make it look up the correct column based on a text or non-sequential number value.

Alex · 12-11-2010, 07:35 AM

How will the cell that triggers the formula be formatted? That is, what exactly would be your formula's input? A single cell with value apple/Ralph? One cell says apple and the next says Ralph?

Anyway, for this array (cell A1 is the blank top left)

Code:

          apple      banana      orange
Ralph     a          b           c
Fred      d          e           f
George    g          h           i

And for simplicity sake let's say that the first half of what you're looking for (e.g. George) is in cell A12 below that array and the second half (e.g. banana) is in B12.

Then in C12 this formula would return h

Code:

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0))

If the input were actually in one cell you'd have put a formula into where this says "B12" and "A12" to parse the input first.

12-10-2010, 10:32 PM	#1
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Alright, next question on lookups... I have fallen in love with VLookups and I use them frequently(although for some reason I have yet to need to use an HLookup function). I am such a geek. I am sure this is another simple one, but my searches haven't provided the results I need. Basically, I need to find the value in an array when value A matches the header across the top and value B matches the list down the side. For example, if I had three column headers; apple, banana, and orange and three row headers; Ralph, Fred, and George. For simplicity's sake, lets say the values (left to right by row) are 1-9 (apple/Ralph is 1, banana/Ralph is 2, orange/Fred is 8, etc.) How do I look up the value based on TWO values - so that the look up knows that when value A is (for example) orange and value B is Fred, that the result is "6"? ETA: for what I needed tonight, I was able to cheat by using a VLookup since the column header info was just the numbers 1-8 (UPS Zones). By using this formula: =VLOOKUP(J3,'[Lookup table 121010.xlsx]Sheet4'!$A$35:$I$42,H3+1,FALSE) In this case, you will see I used the value in H3 (Zone) and added one to it. Since the first column of the array was the value that VLookup was looking for a match on, the second column would be Zone 1, the third Zone 2 and so on. By adding 1 to the value of the zone, it made the formula look in the correct column. I just couldn't figure out how to make it look up the correct column based on a text or non-sequential number value. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln Last edited by Kevy Baby : 12-10-2010 at 10:39 PM.
	Submit to Quotes

12-11-2010, 07:35 AM	#2
Alex . Join Date: Feb 2005 Posts: 13,354	How will the cell that triggers the formula be formatted? That is, what exactly would be your formula's input? A single cell with value apple/Ralph? One cell says apple and the next says Ralph? Anyway, for this array (cell A1 is the blank top left) Code: apple banana orange Ralph a b c Fred d e f George g h i And for simplicity sake let's say that the first half of what you're looking for (e.g. George) is in cell A12 below that array and the second half (e.g. banana) is in B12. Then in C12 this formula would return h Code: =INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0)) If the input were actually in one cell you'd have put a formula into where this says "B12" and "A12" to parse the input first.
	Submit to Quotes