Nesting/Imbedding Vlookup and Hlookup
Posted by Scott on January 30, 2002 8:51 AM
Is there a way to nest/imbed a vlookup within an hlookup or IF function?
Thanks,
Posted by R on January 30, 2002 8:57 AM
Yep, give us some more info....!!!
Posted by for what you're going for? Adam S. (-nt) on January 30, 2002 9:04 AM
does: =if(isna(vlookup(blah))=true,0,vlookup(blah)) qualify
Posted by Scott on January 30, 2002 10:20 AM
Re: Yep, give us some more info....!!!
I want cell C8,D8 & E8 to go look at what is in cell A1, then go to a range and find that same item across a row in a data table, then pull the information from the table for what is in cell A8.
So A1=LO, A8=019
Data table looks like this:
LO GB
resource desc. class cost cost
019 cook labor 10 11
020 clean labor 9 10
I want cell C8 to pull the description for 019,020, etc... D8 to pull the class for 019,020, etc... D9 to know which column to pull the cost from whether I put LO or GB in cell A1.
Does this make sense?
Thanks for your help.
Posted by Aladin Akyurek on January 30, 2002 11:27 AM
VLOOKUP?
Scott --
It looks like that LO stands for th 4th column of your lookup table, GB for the 5th column.
Select all of the cells of your lookup table and name it e.g., COST_TABLE via the Name Box on the Formula Bar (This suggestion is sensible if it's a table that does not change frequently; Otherwise using a different mechanism would be better.).
In A1 you could enter 4 when LO or 5 when GB. If this is possible, then use in
C8: =VLOOKUP(A8,COSTtable,2,0)
D8: =VLOOKUP(A8,COSTtable,3,0)
D9: =VLOOKUP(A8,COSTtable,A1,0)
Is this close to what you want?
Aladin
=======
Posted by Scott on January 30, 2002 1:45 PM
Re: VLOOKUP?
Aladin, I got an answer that worked on another site...thanks for looking at it for me!!
Here is the answer I got:
Here's one way. For simplicity, I'll make some
> assumptions:
> >
> >1) Name the range starting at the 019, and ending four
> columns to the
> >right and down as many rows as you need, "DataTable". I
> assume that the
> >resources are in ascending order
> >
> >2)Name the range with LO and GB (i.e., 1 row by two
> columns)
> >"CostHeaders". I assume that the "Cost Headers" are not
> sorted
> >
> >3) If you get more "Cost Headers" or expand your
> resources, make sure
> >that the named ranges reflect the changes.
> >
> >
> >Then:
> >
> >C8: =VLOOKUP(A8, DataTable, 2, TRUE)
> >D8: =VLOOKUP(A8, DataTable, 3, TRUE)
> >E8: =VLOOKUP(A8,DataTable,MATCH(A1, CostHeaders,0)
> +3,TRUE)
> >
Scott -- D8: =VLOOKUP(A8,COSTtable,3,0) D9: =VLOOKUP(A8,COSTtable,A1,0) : So A1=LO, A8=019 : Data table looks like this
Posted by Aladin Akyurek on January 30, 2002 2:02 PM
Re: VLOOKUP?
You're welcome.
It's not much different from my answer. I doubt that the match-type TRUE (or 1) in VLOOKUP is right [even when DataTable (or COSTtable) is sorted]. I believe FALSE (or 0) is better in your case. Moreover, I consider adding MATCH to VLOOKUP mere cosmetics (with some cost) to compute the column to peek in.
Aladin
======= Here is the answer I got: Here's one way. For simplicity, I'll make some > assumptions: > > > >1) Name the range starting at the 019, and ending four > columns to the > >right and down as many rows as you need, "DataTable". I > assume that the > >resources are in ascending order > > > >2)Name the range with LO and GB (i.e., 1 row by two > columns) > >"CostHeaders". I assume that the "Cost Headers" are not > sorted > > > >3) If you get more "Cost Headers" or expand your > resources, make sure > >that the named ranges reflect the changes. > > > > > >Then: > > > >C8: =VLOOKUP(A8, DataTable, 2, TRUE) > >D8: =VLOOKUP(A8, DataTable, 3, TRUE) > >E8: =VLOOKUP(A8,DataTable,MATCH(A1, CostHeaders,0) > +3,TRUE) > >
