Lookup on two criteria

[Guest]

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

 

[Peo Sjoblom]

http://www.contextures.com/xlFunctions03.html#IndexMatch2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Peo Sjoblom]

http://www.contextures.com/xlFunctions03.html#IndexMatch2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

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.

 

[Guest]

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.

 

[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

 

[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

 

[Guest]

ctrl>shift>enter ???. Just hit Enter it will do the job.

 

[Guest]

ctrl>shift>enter ???. Just hit Enter it will do the job.