Vlookup question

[ruchie]

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?

 

[Bob Phillips]

=INDEX(C1:C00(1,("A1:A100=NJ")*(B1:B100="Average"),0))

where column A is the dept, B the type and C month figures

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ruchie]

i tried it, it says that the formula has too few arguments...

 

[Peo Sjoblom]

Try

=INDEX(C1:C100,MATCH(1,(A1:A100="NJ")*(B1:B100="Average"),0))

still array entered

 

[Bob Phillips]

Sorry, missed a function

=INDEX(C1:C00,MATCH(1,("A1:A100=NJ")*(B1:B100="Average"),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

You missed a 1 as well

INDEX(C1:C100

Peo

Sorry, missed a function

=INDEX(C1:C00,MATCH(1,("A1:A100=NJ")*(B1:B100="Average"),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[ruchie]

the formula was working just fine till i changed the data set a bit.
now even though i chaned it back on to the original, it isnt working
fine. the formulla im using is pasted below:

=INDEX(X!D3106,MATCH(1,(X!A3:A106=$A5)*(X!B3:B106="Total monthly
Average"),0))

X is the name of a worksheet within my workbook from where im
extracting data from.
column D is the data i want to return
column A and B are teh columns on the basis of which i want to return
the data

any possible reason for a #N/A error?

 

[Dave Peterson]

You don't have a row in the X worksheet where A3:A106 matches the value in A5
and b3:b106 = "total monthy average" at the same time.

 

[ruchie]

i do have atleast 1 row matching for each value from A3 to A106

 

[Peo Sjoblom]

If that's the case then there are only 2 options, you didn't enter the
formula with ctrl + shift & enter (should give you curly brackets around the
formula), that will give you an N/A error or that what looks like a match is
not

 

[ruchie]

the bad thing is tht i did both... still this thing is hapenning...
donno why...

 

[ruchie]

i do have 1 such row

 

[Dave Peterson]

Pick out that row that you think matches and find a couple of extra cells and
use a couple of formulas like:

=X!A37=$A5
=X!B37="Total monthly Average"

Where 37 represents the row you think matches your data.

Do you see True in both cells?