Lookup One table to another table

[xlstime]

Hi Master,

How to lookup one table to another table (both tables have more than 5-6 columns and need to validate (match all).

Please refer attachment

 

[NARAYANK991]

Hi ,

Eliminate all the symbols < , = and > from your table Table1.

Symbols in the criteria range are interpreted correctly by Excel , but symbols in the data are interpreted as text strings.

Narayan

 

[p45cal]

Both the following formulae are to entered using the keystrokes Ctrl + Shift + Enter, NOT just Enter:
in Q7:
=INDEX($H$7:$H$15,MATCH(K7 & L7 & M7 & N7 & O7 & P7,$B$7:$B$15 & $C$7:$C$15 & $D$7:$D$15 & $E$7:$E$15 & $F$7:$F$15 & $G$7:$G$15,0))
nd copy down.
In R7:
=INDEX($A$7:$A$15,MATCH(K7 & L7 & M7 & N7 & O7 & P7,$B$7:$B$15 & $C$7:$C$15 & $D$7:$D$15 & $E$7:$E$15 & $F$7:$F$15 & $G$7:$G$15,0))
and copy down.

 

[Nebu]

Hi:

You can use the following non array formula as well.

Q7:=LOOKUP(2,1/((K7=$B$7:$B$15)*(L7=$C$7:$C$15)*(M7=$D$7:$D$15)*(N7=$E$7:$E$15)*(O7=$F$7:$F$15)*(P7=$G$7:$G$15)),$H$7:$H$15)

R7:=LOOKUP(2,1/((K7=$B$7:$B$15)*(L7=$C$7:$C$15)*(M7=$D$7:$D$15)*(N7=$E$7:$E$15)*(O7=$F$7:$F$15)*(P7=$G$7:$G$15)),$A$7:$A$15)

Thanks

 

[bines53]

Hello friends

It is also possible that,

Q7:=LOOKUP(2,1/(MMULT(--(B7:G15=K7 : P7),{1;1;1;1;1;1})=6),H7:H15)

R7=LOOKUP(2,1/(MMULT(--(B7:G15=K7 : P7),{1;1;1;1;1;1})=6),A7:A15)


David

 

[xlstime]

Wow!! this is something new David,

Could you please to understand this formula

Hello friends

It is also possible that,

Q7:=LOOKUP(2,1/(MMULT(--(B7:G15=K77),{1;1;1;1;1;1})=6),H7:H15) R7=LOOKUP(2,1/(MMULT(--(B7:G15=K77),{1;1;1;1;1;1})=6),A7:A15)

I do not know how that face came in the formula!
Should be B7:G15=K77

David

 

[NARAYANK991]

Hi David ,

The funny face is as a result of the colon symbol and P coming together ; so when you type in a worksheet range reference such as K7 through P7 , separate the colon symbol from the P by a space character , as in :

K7 : P7

or , embed the formula using the CODE tags , as in :

Q7:=LOOKUP(2,1/(MMULT(--(B7:G15=K7:P7),{1;1;1;1;1;1})=6),H7:H15) R7=LOOKUP(2,1/(MMULT(--(B7:G15=K7:P7),{1;1;1;1;1;1})=6),A7:A15)

Narayan