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 D3:D96 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

i.e...

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

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

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 D3:D96 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'!D3:D96,0)),"Yes","No")
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2