LOOKUP problem

  • Thread starter Thread starter Tomek
  • Start date Start date
T

Tomek

Could some one have a solution to my problem?

This is the sample:

I have MY_RANGE defined which is 3 rows and 3 columns. I DON'T WANT to name
a header separately (that would be {desc,TOM,MARRY}).


desc TOM MARRY
AGE 10 20
CASH 50 100



In other worksheet I have "CASH" in one cell and "MARRY" in the other.

I need a formula to find 100 as Marry's cash. Vlookup requires 3 as the
column number. How to find that Marry is third in row? I expected
Index(MY_RANGE,1) to return {desc, TOM, MARRY} but it doesn't work.

Thanks in advance
Tomek
 
Hi

With lookup matrix on Sheet1 and lookup values in cell's Cell1 and Cell2
=OFFSET(Sheet1!$A$1,MATCH(Cell1,Sheet1!$A$2:$A$3,0),MATCH(Cell2,Sheet1!$B$1:
$C$1,0))
 
Tormek

Your problem is that you need a HLOOKUP not a VLOOKU

=HLOOKUP(A6,MY_RANGE,3,0) works, where A6 contains the search object, in this cae "MARRY

I hope this helps

Steve K.
 
I believe this method works, but I am trying to find a formula where
MY_RANGE is the only Input Data to formula (not a $a$2:$a$3 or $b$1:$c$1).
In other words a2:a3 is the top row of MY_RANGE, b1:c1 is the left column of
MY_RANGE, thus there should be a method of using some function i.e.
f(MY_RANGE, cell1, cell2) = Marry Cash
 
Steve, thanks for trying but you are using "3" as a parameter which a
different thing each time. I need Excel self to know that Cash is in the
third row of MY_RANGE.

Tomek
 
Hi

The formula
=INDEX(ListA,MATCH(A1,INDEX(ListA,,1)),MATCH(A2,INDEX(ListA,1,)))

where ListA=Sheet1!$A$1:$C$3 (NB! The named range MUST include both row and
column headers) and A1 contains AGE/CASH and A2 contains TOM/MARRY
should do the trick, but it seems there is a problem - when A2="MARRY", then
the part
MATCH(A2,INDEX(ListA,1,))
returns somehow 1 instead 3, and I haven't a clue why.

A possible workaround - define 2 additional ranges for header row and column
(or one for header row only, as likely there are no problems with header
column), but as result we are returned to point where started - we need more
than one range to define. In general, it isn't problem, when on sheet with
ListA aren't any other entries. Then you can define all ranges as relative -
so that you don't need to edit them at all.

ListA=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A,"<>"),COUNTIF(Sheet1!$1:$1,"
<>"))
RowHead=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A,"<>"),1)
ColHead=OFFSET(Sheet1!$A$1,,,1,COUNTIF(Sheet1!$1:$1,"<>"))

The formula will be
=INDEX(ListA,MATCH(A1,RowHead),MATCH(A2,ColHead))
or
=OFFSET(Sheet1!$A$1,MATCH(A1,RowHead)-1,MATCH(A2,ColHead)-1)

Even better will be to define the named ranges as
ListA=OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,COUNTIF(Sheet1!$1:$1
,"<>")-1)
RowHead=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A,"<>"),1)
ColHead=OFFSET(Sheet1!$B$1,,,1,COUNTIF(Sheet1!$1:$1,"<>")-1)

with formula
=INDEX(ListA,MATCH(A1,RowHead),MATCH(A2,ColHead))
or
=OFFSET(Sheet1!$A$1,MATCH(A1,RowHead),MATCH(A2,ColHead))

Now jou can format cells A1 and A2 using Data.Validation.List with
A1.Source=RowHead
and
A2.Source=ColHead

Now you can expand your matrix on Sheet1 as you need, and immediately new
selections are available for cells A1 and A1 - and the formula is returning
new values. Only 2 limitations for you there:
1. On Sheet1, no other entries except your matrix, are allowed
2. No gaps (empty rows or columns) in matrix are allowed
 
Back
Top