Lookup on two criteria

K

Kathryn

Dear all

I would like to create a lookup or index that finds a particular entry by
column and row. This is my data

part number month1 month2 month3 month4
123 10 20 30 40
456 50 60 70 80


On another sheet I have two selection cells, I have selected part number 123
and month 2, in another cell I want to see the result 20.

I hope this makes sense.

I really appreciate any help as I am relatively new to excel

Kathryn
 
D

DaveO

Now this is slightly tricky Kathryn and without knowing what your other sheet
like it's hard for me to write the formula for you.

However I can tell you I'd be using an =INDIRECT(ADDRESS()) as my start
point. I'd then use a MATCH to get the row and another MATCH to get the
column numbers.

If you look these 3 functions up in Help you should get pretty close to
being able to do it yourself.

Sorry I can't be of more help, but here's how I guess it'd look...

=INDIRECT(ADDRESS(MATCH({PART NO CELL}, A1:A100, 0), MATCH({MONTH NO CELL},
A1:Z1, 0)))

Hope this helps.
 
S

shail

hi Kathryn,

You will get lots of functions over this query.
Say you will enter "123" at A6 and "month2" at cell B6. Now -

1. INDEX & MATCH

=INDEX(A1:E3,MATCH(A6,A1:A3,0),MATCH(B6,A1:E1,0))

You need to Array Enter this <CTRL><SHIFT><ENTER>


2. SUMPRODUCT

=SUMPRODUCT((A2:A3=A6)*(B1:E1=B6)*(B2:E3))

You need to Array Enter this <CTRL><SHIFT><ENTER>


3. OFFSET & MATCH

=OFFSET(A1,MATCH(A6,A2:A3,0),MATCH(B6,B1:E1,0))

You need to Array Enter this <CTRL><SHIFT><ENTER>

4. SUM & IF

=SUM(IF(A2:A3=A6,IF(B1:E1=B6,B2:E3)))

You need to Array Enter this <CTRL><SHIFT><ENTER>


5. And lastly but not the least, it is the most interesting and easy
answer for the query you have asked here.

You need to ENABLE "Accept labels in forulas" first. Click
Tools>Options>Calculation>Workbook Options - check the "accept labels
in formulas" checkbox.

Now when you enter at any cell this way -

=123 month2

you will get 20.

Isn't this interesting.


Thanks,

Shail
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top