Vlookup question

  • Thread starter Thread starter ruchie
  • Start date Start date
R

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?
 
=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)
 
Try

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

still array entered
 
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)
 
You missed a 1 as well

INDEX(C1:C100

Peo


Bob Phillips said:
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)
Click to expand...
 
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!D3:D106,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?
 
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.
 
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
 
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?
 
Back
Top