HLOOKUP

[Steved]

Hello from Steved

=HLOOKUP(A1:B1,C4:AT125,3)
would like it to be
=HLOOKUP(A1:A2,C4:AT125,3)

The above formula works fine on A1
I want to change B1 in the formula to A2 so that it looks
in both cells to give me the value of the 3rd cell. in
other words is it possible for a double Hlookup. The above
finds the answer in C4 and gives the require answer in C6.
This formula would work fine but for it's limitations for
example the cell a1 then A2 looks up the information from
C4:AT125 and returns the information from the third cell
down to A3. Please Help

Thankyou.

 

[Max]

It's a little tough to understand what you're after from your post
despite several readings <g>

Think your lookup value syntax in HLOOKUP is not correct, viz
it should not be an array (A1:B1), it should be a single cell ref, eg: A1

It's also quite meaningless even if you had array-entered the HLOOKUP
as only the 1st cell (A1) of the lookup "array" (A1:B1) would be read by the
formula.

Here's one guess at what you're after:

you have a lookup value in A1,
and you want to retrieve a value
down the col in the lookup table corresponding to A1,
say, down by a certain number of rows
which number is specified in A2,

the formula : =HLOOKUP(A1,C4:AT125,A2,FALSE)
will return the answer

 

[Kanga]

I wonder if you need to concatenate your HLOOKUP
ie, perform 2 x HLOOKUPS in the one cell. To do this you just put "&"
in between the VLOOKUPS (ie, you click =, then HLOOKUP, answer the 4
questions then type "&" then click =, then HLOOKUP, answer the 4
questions).
Just a thought! Regards, Kanga

 

[Steved]

Hello Max from Steved
Thankyou

I am just starting to learn about lookups
Yes you understood what I wanted to acheive.

I have one issue and please excuse my knowledge but it is
asking me for a VALUE.

Could you steer on the right direction
A1 is Text
A2 is Text
A3 returns a number

 

[Max]

If you got a #VALUE! error value using the example HLOOKUP
I gave (with the 4th parameter set to FALSE), that's probably
because the HLOOKUP cannot find an exact match for
the lookup_value specified in A1.

a. If the lookup_value in A1 is text, try a slight amendment
: =HLOOKUP(TRIM(A1),C4:AT125,A2,FALSE)

b. If the lookup_value in A1 is numeric, try changing the
4th parameter to TRUE, instead of FALSE, viz use something like
: =HLOOKUP(A1,C4:AT125,A2,TRUE)

For TRUE, you have to ensure that the first row of the table_array,
i.e. C4:AT4 is sorted in ascending order (left to right) first.

Check up Excel's HELP for more info on HLOOKUP

cheers