Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
I have to search and put values in one of my sheets after looking in
a field of a field. I believe i need to to a nested vlookup, but im not sure how to go about it. I copied the relevant portions of the data below: Department PriceType Monthly Average NY NY Average $1,000.00 NJ NJ NJ NJ Average $1,500.35 I have to search within a department (for example NJ), and then search where average is occuring in the Price Type field, and then pick the corresponding Monthly average value(1500.35 in this case) and put it in the vlookup formula origination sheet. Any help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
One shot at this ..
Assuming source data as posted is in a sheet: X, data from row2 down with col A = Dept, col B = Pricetype, col C = Monthly av In your other sheet, Assume depts (NY, NJ, etc) are listed in A2 down, Put this in B2, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =INDEX(X!$C$2:$C$100,MATCH(1,(X!$A$2:$A$100=A2)*(X !$B$2:$B$100="Average"),0)) Copy B2 down to return the required figs from col C in X. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ruchie" wrote in message ups.com... I have to search and put values in one of my sheets after looking in a field of a field. I believe i need to to a nested vlookup, but im not sure how to go about it. I copied the relevant portions of the data below: Department PriceType Monthly Average NY NY Average $1,000.00 NJ NJ NJ NJ Average $1,500.35 I have to search within a department (for example NJ), and then search where average is occuring in the Price Type field, and then pick the corresponding Monthly average value(1500.35 in this case) and put it in the vlookup formula origination sheet. Any help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
thanks max! it worked perfectly fine! ur the best!
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
welcome. good to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ruchie" wrote in message ps.com... thanks max! it worked perfectly fine! ur the best! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
max,
i ran into problem with the formula is suddenly stopped working after i changed the data set a bit ive been using this formula: =INDEX(X!D3:D106,MATCH(1,(X!A3:A106=$A5)*(X!B3:B10 6="Flat/Tier/Step Average"),0)) Could you tell me what the problem could be? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
What do you mean by "stopped working"? I can't see anything there that would
make it stop working. Did you array enter it? -- Regards, Peo Sjoblom "ruchie" wrote in message oups.com... max, i ran into problem with the formula is suddenly stopped working after i changed the data set a bit ive been using this formula: =INDEX(X!D3:D106,MATCH(1,(X!A3:A106=$A5)*(X!B3:B10 6="Flat/Tier/Step Average"),0)) Could you tell me what the problem could be? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
yup i did!!! it just stopped for no reason!
let me give some background of X sheet... the data in X are basically links from a pivot table which i created. since index/match wasnt able to search from the pivot table earlier, thats why i made a new sheet called X whereby i did a paste special- paste links to it. it was working fine uptil today, when i altered the pivot table's source data a bit, and refreshed it. even after i changed the data on the X file to reflect the new and current information, and edited the formula like you see above, its showing me the #N/A result. why is that hapenning? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
the problem is with the Match function. I just looked into the
formula's calculations. just dont know why the error is coming though.. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested VLOOKUP
It's because it cannot find a match, you might want to use something like
=IF(ISNA(MATCH(....),"",INDEX(...,MATCH(....))) which will return a blank if no match is found -- Regards, Peo Sjoblom "ruchie" wrote in message oups.com... the problem is with the Match function. I just looked into the formula's calculations. just dont know why the error is coming though.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with nested IF | Excel Discussion (Misc queries) | |||
Can Someone Help me With a Nested VLOOKUP | Excel Discussion (Misc queries) | |||
Nested vlookup | Excel Discussion (Misc queries) | |||
nested ifs or vlookup or ? | Excel Discussion (Misc queries) | |||
Nested vlookup? | Excel Worksheet Functions |