ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Lookup Function (https://www.excelbanter.com/excel-discussion-misc-queries/110671-lookup-function.html)

kmwhitt
Lookup Function
 
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
Lookup Function
 
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

"kmwhitt" wrote in message
...
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

kmwhitt
Lookup Function
 
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" wrote:

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

"kmwhitt" wrote in message
...
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
Lookup Function
 
=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

"kmwhitt" wrote in message
...
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" wrote:

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

"kmwhitt" wrote in message
...
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

kmwhitt
Lookup Function
 
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" wrote:

=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

"kmwhitt" wrote in message
...
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" wrote:

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

"kmwhitt" wrote in message
...
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
Lookup Function
 
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

"kmwhitt" wrote in message
...
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" wrote:

=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

"kmwhitt" wrote in message
...
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" wrote:

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

"kmwhitt" wrote in message
...
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

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

Kevin

"Biff" wrote:

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

"kmwhitt" wrote in message
...
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" wrote:

=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

"kmwhitt" wrote in message
...
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" wrote:

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

"kmwhitt" wrote in message
...
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
Lookup Function
 
You're welcome. Thanks for the feedback!

Biff

"kmwhitt" wrote in message
...
Thanks for all your help, Biff! I meant to use CONCATENATE and I now have
it
working....

Kevin

"Biff" wrote:

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

"kmwhitt" wrote in message
...
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" wrote:

=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

"kmwhitt" wrote in message
...
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" wrote:

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

"kmwhitt" wrote in message
...
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

kmwhitt
Lookup Function
 
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

"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"kmwhitt" wrote in message
...
Thanks for all your help, Biff! I meant to use CONCATENATE and I now have
it
working....

Kevin

"Biff" wrote:

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

"kmwhitt" wrote in message
...
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" wrote:

=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

"kmwhitt" wrote in message
...
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" wrote:

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

"kmwhitt" wrote in message
...
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


All times are GMT +1. The time now is 11:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com