MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Index/Match function.


Posted by Mike on November 09, 2001 12:40 PM

I have a simple on ehere which is driving me nuts.
Just trying out the Index/Match function and I keep getting a #N/A.
All I want to do is enter the P/N in a cell on
worksheet B, have the formula go to go to
worksheet A (Sheet Labeled "Hardware" , find the P/N and pull the desc.
Works fine with the VLOOKUP.

Sheet A lists all the Hardware I will need. (P/N in Col A, and Desc in Col B).

I write my formula on worksheet B as follows:
=INDEX(Hardware!$A$3:$A$180,MATCH(K366,Hardware!$B$3$B$180,False),1)

I figure it reads; GOTO "Hareware" Sheet and look in Range A3-A180 for a Match on sheet B at K336 (which is the P/N). Locate the P/N on the Sheet A, in Col B and Pull the Desc.

Not.

Does anyone see my error?

Thanks,
Mike


Posted by Juan Pablo on November 09, 2001 12:43 PM

The problem is with the MATCH function. Last argument isn't (As in VLOOKUP True or false), there are three options, 1, 0, -1. You're looking for the 0. That should solve it.

Also, to get column B you should set last argument of INDEX to 2 instead of 1, and MATRIX to $A$3:$B$180.

Juan Pablo

Posted by Mike on November 09, 2001 1:00 PM

Tried your suggestion Juan and the formula now reads =INDEX(Hardware!$A$3:$A$180,MATCH(K366,Hardware!$B$3:$B$180,0),2).

Still the same result. I tried all diff combo's
and still the same result. Try again.


* * * * * * * * *

Posted by Aladin Akyurek on November 09, 2001 1:03 PM

I think it's

=INDEX(Hardware!$B$3:$B$180,MATCH(K366,Hardware!$A$3:$A$180,0))

Aladin

Posted by Juan Pablo on November 09, 2001 1:03 PM

Miss one small part, try with this one.

=INDEX(Hardware!$A$3:$B$180,MATCH(K366,Hardware!$B$3:$B$180,0),2)

This should work... by the way, why don't you wanna use the VLOOKUP ?

=VLOOKUP(K366,Hardware!$A$3:$B$180,2,0)

or, to avoid errors:

IF(COUNTIF(Hardware!$A$3:$A$180,K366),VLOOKUP(K366,Hardware!$A$3:$B$180,2,0),"")

Juan Pablo

Posted by Mike on November 09, 2001 1:12 PM

Well That didn't do it either. Could I be missing something in the Syntax? Going to try performing the Match funtion by itself and see what happens. I really appreciate your input.
Please try it yourself and see what happens.

I'll let you know if the Match works by itself.

Mike

* * * * * * * * *


Posted by Mike on November 09, 2001 1:17 PM

Juan:
Tried that scenario. No luck.
Bill Jelen posted tips on his MrExcel site and I just thought I'd try it (Index Function).

The VLOOKUP worked just fine and that's what I incorporated into the Worksheet.

Will try the MATCH functino now.

* * * * * * * *

Posted by Aladin Akyurek on November 09, 2001 1:22 PM

Try

=COUNTIF(Hardware!$A$3:$A$180,K366)

If this gives zero, then maybe whatever value is in K366 does not exist.
What is P/N? A number or an alphanumeric token (e.g., S1008)?

Aladin

======= Well That didn't do it either. Could I be missing something in the Syntax? Going to try performing the Match funtion by itself and see what happens. I really appreciate your input.