CODEWITHSUNDEEP
excelexcel-formula
IronAces
IronAces

Reputation: 1893

VLOOKUP across two worksheets (with an IF?)

Doing a simple VLOOKUP on a cell to return a date from another worksheet like below

=VLOOKUP(A46,'ECA AW12'!B92:AO133,15, 0)

Essentially, look up cell A46, and check it against B92:AO133 on the sheet ECA AW12, if true then return value of cell 15 on same row of table as match

However… what if A46 isn’t on that worksheet? It can appear in one of two spreadsheets.

I suppose logically addressing it, an function would be required to search for the value in the first spreadsheet, and if it isn't there perform a VLOOKUP on the second sheet.

Any help is greatly appreciated,

Upvotes: 2

Views: 14220

Answers (2)

Jook
Jook

Reputation: 4692

So you are looking for the ISERROR function in combination with IF.

Would look like this

=IF(ISERROR(VLOOKUP(A46,'ECA AW12'!B92:AO133,15, 0)),        
    VLOOKUP(A46,'OtherWorksheet'!B92:AO133,15, 0),
    VLOOKUP(A46,'ECA AW12'!B92:AO133,15, 0),

Upvotes: 3

Romain
Romain

Reputation: 1302

You can first test if A46 is indeed present in ,'ECA AW12'!B92:AO133 and then decide wether to search on this page or fallback to the second one. The simplest way to do it would be to use the MATCH function to find the value in the orignial array, and test with ISERROR if it has indeed been found. If MATCH returns an error, then VLOOKUP in the other sheet, if not, VLOOKUP in the original sheet.

=IF(ISERROR(MATCH(A46,`'ECA AW12'!B92:AO133`,0)),VLOOKUP(A46,'OTHER SHEET'!B92:AO133,15, 0),VLOOKUP(A46,'ECA AW12'!B92:AO133,15, 0))

Upvotes: 0

Related Questions