2-Dim Lookup
 

Hi,

I've been using VLookup and it works well. But now I'd like Excel to choose
the cell that matches both the vertical and horizontal indexes. IE I'd like
it to choose the formula or value that is, say, below the value 3 (HLookup)
AND to the right of value 51 (as VLookup).

Can anyone help me?

Thanks in advance.

Jeff

Hi
use
=INDEX(A1:G20,MATCH(lookup_1,A1:A20,0),MATCH(looku p_2,A1:G1,0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Jeff Spiccoli" schrieb im
Newsbeitrag ...
Hi,

I've been using VLookup and it works well. But now I'd like Excel to
choose
the cell that matches both the vertical and horizontal indexes. IE
I'd like
it to choose the formula or value that is, say, below the value 3
(HLookup)
AND to the right of value 51 (as VLookup).

Can anyone help me?

Thanks in advance.

Jeff

Frank,

I gave it an honest effort, but couldn't get it to work.

My table is located in D5:I10. (Though, D5 itself is blank, of course.) I
want the value in column D (D6:D10) to match cell C2, and the value in row 5
(E5:I5) to match cell D2. Formula would return the intersecting cell value,
for example, H9.

If you could give me the precise formula to type in I'd sure appreciate it.

Thanks,

Jeff






"Frank Kabel" wrote:

Hi
use
=INDEX(A1:G20,MATCH(lookup_1,A1:A20,0),MATCH(looku p_2,A1:G1,0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Jeff Spiccoli" schrieb im
Newsbeitrag ...
Hi,

I've been using VLookup and it works well. But now I'd like Excel to
choose
the cell that matches both the vertical and horizontal indexes. IE
I'd like
it to choose the formula or value that is, say, below the value 3
(HLookup)
AND to the right of value 51 (as VLookup).

Can anyone help me?

Thanks in advance.

Jeff

=INDEX(E6:I10,MATCH(C2,D6:D10,0),MATCH(D2,E5:I5,0) )

Hope this helps!

Jeff Spiccoli Wrote:
Frank,

I gave it an honest effort, but couldn't get it to work.

My table is located in D5:I10. (Though, D5 itself is blank, of
course.) I
want the value in column D (D6:D10) to match cell C2, and the value in
row 5
(E5:I5) to match cell D2. Formula would return the intersecting cell
value,
for example, H9.

If you could give me the precise formula to type in I'd sure appreciate
it.

Thanks,

Jeff






"Frank Kabel" wrote:

Hi
use
=INDEX(A1:G20,MATCH(lookup_1,A1:A20,0),MATCH(looku p_2,A1:G1,0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Jeff Spiccoli" schrieb im
Newsbeitrag
...
Hi,

I've been using VLookup and it works well. But now I'd like Excel
to
choose
the cell that matches both the vertical and horizontal indexes.
IE
I'd like
it to choose the formula or value that is, say, below the value 3
(HLookup)
AND to the right of value 51 (as VLookup).

Can anyone help me?

Thanks in advance.

Jeff




--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275005

Thanks, Domenic! It works great.

I had D5 instead of E6.

Hey, what if the numbers aren't an exact match. How do I have it choose the
number above, below, closest, etc?

Jeff


"Domenic" wrote:


=INDEX(E6:I10,MATCH(C2,D6:D10,0),MATCH(D2,E5:I5,0) )

Hope this helps!

Jeff Spiccoli Wrote:
Frank,

I gave it an honest effort, but couldn't get it to work.

My table is located in D5:I10. (Though, D5 itself is blank, of
course.) I
want the value in column D (D6:D10) to match cell C2, and the value in
row 5
(E5:I5) to match cell D2. Formula would return the intersecting cell
value,
for example, H9.

If you could give me the precise formula to type in I'd sure appreciate
it.

Thanks,

Jeff






"Frank Kabel" wrote:

Hi
use
=INDEX(A1:G20,MATCH(lookup_1,A1:A20,0),MATCH(looku p_2,A1:G1,0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Jeff Spiccoli" schrieb im
Newsbeitrag
...
Hi,

I've been using VLookup and it works well. But now I'd like Excel
to
choose
the cell that matches both the vertical and horizontal indexes.
IE
I'd like
it to choose the formula or value that is, say, below the value 3
(HLookup)
AND to the right of value 51 (as VLookup).

Can anyone help me?

Thanks in advance.

Jeff




--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275005

Jeff Spiccoli Wrote:
Hey, what if the numbers aren't an exact match. How do I have it choose
the
number above, below, closest, etc?

To find the closest match, you would have to change the "Match Type"
(3rd argument) in your MATCH function from 0 to either 1 for the
largest value that's less than or equal to the lookup value (the lookup
array must be arranged in ascending order) or -1 for the smallest value
that's greater than or equal to the lookup value (the lookup array must
be arranged in descending order).


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275005

Thanks, Domenic, I'll give that a try. :)

Best regards,

Jeff


"Domenic" wrote:


Jeff Spiccoli Wrote:
Hey, what if the numbers aren't an exact match. How do I have it choose
the
number above, below, closest, etc?

To find the closest match, you would have to change the "Match Type"
(3rd argument) in your MATCH function from 0 to either 1 for the
largest value that's less than or equal to the lookup value (the lookup
array must be arranged in ascending order) or -1 for the smallest value
that's greater than or equal to the lookup value (the lookup array must
be arranged in descending order).


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275005