| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| numerical integration | Excel Discussion (Misc queries) | |||
| Lookup Function help | Excel Discussion (Misc queries) | |||
| Lookup Function Problems | Excel Worksheet Functions | |||
| lookup function 1 | Excel Worksheet Functions | |||
| How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
Linear Mode
