Help matching combined text cells

[Jay07]

I'm trying to do a VLOOKUP based on a combination of cells...

In Sheet1:
column B is a 4 digit number
column G is text

=B7&""&G7 ....which looks like "3433Roofs"

In a sheet named AcivicoTable:
column B3:B96 is the same 4 digit numbers
column D396 is text


So I need a formula that will look up the combination of text cells fro
sheet one against all of the combination of text cells in sheet 2

i.e...

=IF(VLOOKUP(B7&""&G7,AcivicoTable!B3&""&B396&""&D96,1,FALSE),"Yes","No")

I doubt whether a VLOOKUP can even do this to be honest so any hel
would be greatly appreciated

 

[Claus Busch]

Hi Jay,

Am Thu, 11 Oct 2012 12:51:05 +0000 schrieb Jay07:

In Sheet1:
column B is a 4 digit number
column G is text

=B7&""&G7 ....which looks like "3433Roofs"

In a sheet named AcivicoTable:
column B3:B96 is the same 4 digit numbers
column D396 is text

So I need a formula that will look up the combination of text cells from
sheet one against all of the combination of text cells in sheet 2

try:
=IF(ISNUMBER(MATCH(B7&G7,'Acivico Table'!B3:B96&'Acivico Table'!D396,0)),"Yes","No")
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch