match columns and find the matched Part number

**rajivs27** · 02-16-2010, 09:34 AM

I have 2 input cells, eg: A1 and b1 where I can input 2 neumeraicls.

I also have a table in the same sheet with different numbers on 2 coloms and part numbers on the third colom. eg:c1 to c10 and d1 to d10 and part numbers on e1 to e 10.

If 1 put sizes in a1 and b1, i need to look up c1 to c1 and d1 to d10 and when a match is found use the part number from e1 to e10. eg: i type in 800 in A1 and 600 in B1, I need to match A1 (800) with c1 to C10 and match B1 (600) with d1 to d10. the match will be on a single row and colom e will have the corresponding part number which as to be the ourput.

I have been struggling with this. Any help is greatly appreciated.

**NBVC** · 02-16-2010, 09:37 AM

Try:

=INDEX(E1:E10,MATCH(1,INDEX((C1:C10=A1)*(D1:D10=B1),0),0))

if the Part Number is actually numeric, not alphanumeric, then you can also use:

=SUMPRODUCT(E1:E10,--(C1:C10=A1),--(D1:D10=B1))

**martindwilson** · 02-16-2010, 09:41 AM

=index($e$1:$e$10, match(a1 & b1, index($c$1:$c$10 & $d$1:$d$10, 0), 0))

**rajivs27** · 02-16-2010, 09:56 AM

@NBVC - Thanks your formula worked. @ MartinWilson - Thanks to your formula too. However I have not yet tried it.

I have one more issue now. In the f1 to f10 I also have a description of the part. I need to have the description in the cell adjoining the part number cell. Should I use Vlookup or is there some other innovative way to do this too?

**NBVC** · 02-16-2010, 10:09 AM

You can use Vlookup on the result you just got with formulas above... which would be more efficient

... or you can use the same formula replacing E1:E10 with F1:F10:

=INDEX(F1:F10,MATCH(1,INDEX((C1:C10=A1)*(D1:D10=B1),0),0))