Re HLOOKUP

[Steved]

Hello from Steved the below formula came fom Max
Please I am learning my way round so please bear with me

=HLOOKUP(A1,C4:AT125,A2,FALSE)it is returning #VALUE!
The above formula goes in A3

Column
A1 is text Britomart I type in
A2 is text Barrack Rd I type in
A3 is number returns the Answer 12.6

I would like the above formula to lookup Britomart then
Barrack Rd and return the 12.6 as the example show's you,
it finds Britomart in G12 and then finds Barrack Rd in G13
and finally gets 12.6 from G14

Column
G12 Britomart
G13 Barrack Rd
G14 12.6

Thankyou

 

[Max]

Have you read my reply to you in your earlier post? cheers.

 

[Steved]

Hello I must have sent this as you were replying Thankyou

Okay I have tried =HLOOKUP(TRIM(A1),C4:AT125,A2,FALSE)
But it still gives a #REF!

A1 is St Marys
A2 is School
A3 is =HLOOKUP(TRIM(A1),C4:AT125,A2,FALSE)
What I have done is I typed in St Marys on C4 and School
on C5 and in C6 is 13.5 What I then done was remove what I
typed in C4,C5,C6 which gave me #N/A I typed in School in
C5, still #N/A I then typed in 13.5 in C6 still #N/A but
when I typed in St Marys in C4 it change to #REF!.

Okay is there another way that what i type in A1 and A2
tha it will lookup C4:AT125 and give me the number in the
third row down

Thanks

 

[Max]

ok, think I finally caught your drift...

Try these amendments to your set-up:

Put in C3: =TRIM(C4&"_"&C5)
Copy across C3:AT3
(the above will create a single "merge" field
which combines both values in C4:AT5
for use as the lookup_value row
in the HLOOKUP table_array)

Now put in A3
: =HLOOKUP(TRIM(A1&"_"&A2),C3:AT125,4,FALSE)

A3 should now return what you want

cheers
Max

 

[Steved]

Thanks

-----Original Message-----
ok, think I finally caught your drift...

Try these amendments to your set-up:

Put in C3: =TRIM(C4&"_"&C5)
Copy across C3:AT3
(the above will create a single "merge" field
which combines both values in C4:AT5
for use as the lookup_value row
in the HLOOKUP table_array)

Now put in A3
: =HLOOKUP(TRIM(A1&"_"&A2),C3:AT125,4,FALSE)

A3 should now return what you want

cheers
Max





.

 

[Max]

you're welcome, Steved!