Lookup Function

[Guest]

I have a simple table with 4 rows and 3 columns on one worksheet - A4:C7. On
another worksheet I would like to lookup a value from the table and have it
show up in cell F2. The column and row headings are NOT numbers. They are
text, so I don't think HLOOKUP or VLOOKUP will help. Correct? What function
is best suited to accomplish this?

Thanks,

Kevin

 

[Biff]

They are text, so I don't think HLOOKUP or VLOOKUP will help. Correct?

No, you can use TEXT in lookup tables. Try it!

Biff

 

[Guest]

Thanks, Biff....

I can get the function to work when I use a number, but not when I am
referencing the contents of a cell.

For example:

=HLOOKUP($C$4,INDIRECT("'" & C1 & "'!A4:C7"),"$C$3",FALSE)

I've also tried:

=HLOOKUP($C$4,INDIRECT("'" & C1 & "'!A4:C7"),$C$3,FALSE)

and

=HLOOKUP($C$4,INDIRECT("'" & C1 & "'!A4:C7"),"Stain Only",FALSE)
where Stain Only is a column header in my table.

This works:

=HLOOKUP($C$4,INDIRECT("'" & C1 & "'!A4:C7"),2,FALSE)
but does not accomplish what I want.

Please advise. Thanks again,

Kevin

 

[Biff]

=HLOOKUP($C$4,INDIRECT("'" & C1 & "'!A4:C7"),$C$3,FALSE)

Try this:

=HLOOKUP($C$4,INDIRECT("'" & C1 & "'!A4:C7"),MATCH($C$3,INDIRECT("'" & C1 &
"'!A4:A7"),0),0)

Biff

 

[Guest]

Thanks, that helps tremendously... However, I have another problem. Would
you please evaluate this formula for me:

=INDEX(Multipliers!B5:C7,MATCH($C$3,Multipliers!A5:A7,0),MATCH(INDIRECT("'"&C1&" "&C2&" "&C4&""),Multipliers!B4:C4,0))

I keep getting a #REF! error and I cannot figure it out..... I'll be more
than happy to post the file if needed.

Thanks again,

Kevin

 

[Biff]

INDIRECT("'"&C1&" "&C2&" "&C4&"")

I don't know what's in those cells but you don't have a matched pair of '
(single quotes) for the sheet name or the ! (exclamation mark, sheet name
delimiter).

Biff

 

[Guest]

Thanks for all your help, Biff! I meant to use CONCATENATE and I now have it
working....

Kevin

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks for all your help, Biff! I meant to use CONCATENATE and I now have
it
working....

Kevin

 

[Guest]

One more thing, Biff... since I'm up way past my bedtime. I'm not sure how
to link to other posts, but I have a question about a worksheet event. Would
you be so kind as to check out my latest post and give me some feedback?

Thanks (I owe you big),

Kevin