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

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="cr it2"))

HTH
--
AP

"Andrewllan" a écrit dans le message
de news: ...
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

Hi Ardus,

I think the SUMPRODUCT could be dangerous - if there is more than one
match they'll be summed, whereas INDEX/MATCH would find the first match
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 then
just use a VLOOKUP or INDEX/MATCH to lookup concatenated
value.....or...

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=555973

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




"daddylonglegs" wrote:


Hi Ardus,

I think the SUMPRODUCT could be dangerous - if there is more than one
match they'll be summed, whereas INDEX/MATCH would find the first match
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 then
just use a VLOOKUP or INDEX/MATCH to lookup concatenated
value.....or...

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=555973

Try in C1 sheet 1

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=555973