Question 2 HLookup

  • Thread starter Thread starter Jon Sim`kJonFY51-N4BC15YN-XHH3N3RK-XRSR7WB4
  • Start date Start date
J

Jon Sim`kJonFY51-N4BC15YN-XHH3N3RK-XRSR7WB4

On my job, I have 2 different pay scales. I want to use the "hlookup" function if possible. I will try to give a
example:

a b c d
1 c 80 82 100
2 90 93 95
3 b 80 82 100
4 85 88 89
5
6
7
8 I want to do the following: In A5 I want to type the letter c. In A6 I want to type in the number 82.
In A7, I want the answer 93. Now, if I type the letter b in A5 and I put the number 82 in A6, I want
the answer to be 88 in A7. Rows 1 and 3 are item amounts. Rows 2 and 4 are money amounts.
Can this be done with Hlookup? Is there a easier way?
Thank you for your time
 
One possible way ..

Try in A7:

=IF(ISNA(MATCH(A5,$A$1:$A$4,0)),"",OFFSET(INDIRECT("A"&MATCH(A5,$A$1:$A$4,0)
),1,MATCH(A6,INDIRECT(MATCH(A5,$A$1:$A$4,0)&":"&MATCH(A5,$A$1:$A$4,0)),0)-1)
)

Note:
a. The letters "c", "b" are assumed to be unique entries within the range
$A$1:$A$4
b. The numbers in the same row to the right of the letters "c", "b" are also
assumed unique

The above will return the desired results indicated in your post

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
Jon Sim`kJonFY51-N4BC15YN-XHH3N3RK-XRSR7WB4 said:
On my job, I have 2 different pay scales. I want to use the "hlookup"
Click to expand...
function if possible. I will try to give a
 
Maybe a slight improvement to error-check the entry in A6 as well ..
(besides A5, for which error-checking is included in the earlier suggested
formula)

Try instead in A7:

=IF(OR(ISNA(MATCH(A5,$A$1:$A$4,0)),ISNA(MATCH(A6,INDIRECT(MATCH(A5,$A$1:$A$4
,0)&":"&MATCH(A5,$A$1:$A$4,0)),0))),"",OFFSET(INDIRECT("A"&MATCH(A5,$A$1:$A$
4,0)),1,MATCH(A6,INDIRECT(MATCH(A5,$A$1:$A$4,0)&":"&MATCH(A5,$A$1:$A$4,0)),0
)-1))

The error-checks on the entries in A5 and A6 will ensure that "blanks", ie
""
are returned if either A5 and/or A6 contain invalid entries
(entries for which there are no matches in the referenced data)
instead of #NAs
 
Your lookup table has a redundant set up. If you change it to:

1 blank 80 82 100
2 c 90 93 95
3 b 85 88 89

you can have:

=INDEX($B$2:$D$4,MATCH(A5,$A$2:$A$4,0),MATCH(A6,$B$1:$D$1))

with A5 housing "b" and A6 82.

With your current setup, it's:

=INDEX($B$1:$D$4,MATCH(A5,$A$1:$A$4,0)+1,MATCH(A6,INDEX($B$1:$D$4,MATCH(A5,$
A$1:$A$4,0),0)))

Jon Sim`kJonFY51-N4BC15YN-XHH3N3RK-XRSR7WB4 said:
On my job, I have 2 different pay scales. I want to use the "hlookup"
Click to expand...
function if possible. I will try to give a
 
Back
Top