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.
