Excel Question #46

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-11-2010, 07:35 AM	#11
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