Look up table

A

Andrewllan

How do I get a lookup table to search for two critera and not just one
Can this be done by using =if (and())


Thanks Andrew
 
A

Ardus Petus

No, You must use SUMPRODUCT if searched value is numeric or INDEX/MATCH if
aplha/num

=SUMPRODUCT((A1:A99="crit1"))*(B1:B99="crit2");C1:C99)

=INDEX(C1:C99,MATCH(1,(A1:A99="crit1")*(B1:B99="crit2"))

HTH
 
D

daddylonglegs

Hi Ardus,

I think the SUMPRODUCT could be dangerous - if there is more than on
match they'll be summed, whereas INDEX/MATCH would find the first matc
only. Also should be

=INDEX(C1:C99,MATCH(1,(A1:A99="crit1")*(B1:B99="cr it2"),0))

needs to be confirmed with CTRL+SHIFT+ENTER

Other options would be to use another column to concatenate and the
just use a VLOOKUP or INDEX/MATCH to lookup concatenate
value.....or...

=LOOKUP(2,1/((A1:A99="crit1")*(B1:B99="cr it2")),C1:C99)

which gives the last match if more than one...
 
A

Andrewllan

The probelm I have is that say on sheet1
A1 = Crit1
b1 = crit2
I then want to search a 2nd sheet so the value in A1 finds a match in column
1 and then match b1 with the value in the row and show the amount that
corressponds to this which is in a third row
So I have Angilan in a1 and b2 = 15mm .
I then want to find on the next sheet Angilan in col1 then 15mm in the row
that corresponds with anglian and then insert this value in sheet 1.

Thanks Andrew
 
D

daddylonglegs

Try in C1 sheet 1

=LOOKUP(2,1/((Sheet2!A1:A99=A1)*(Sheet2!B1:B99=B1)),Sheet2!C1:C99)
 
Top