Help to Simplify a VLOOKUP formula

[cooper645]

Hi all,

BLUF: The formula i have works but is messy.

Win 7, Excel 2010.

the formula as follows:

=IF(ISNA(IF(ISNA(VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0)),VLOOKUP($R$7,'LE(A)2029B'!$A$17:$S$25000,3,0),VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0))),VLOOKUP($R$7,'LE(A)2029D'!A17:$S$25000,3,0),IF(ISNA(VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0)),VLOOKUP($R$7,'LE(A)2029B'!$A$17:$S$25000,3,0),VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0)))

What it does:

The formula is in a Cell on Sheet 1, it uses the contents of cell R7 on sheet 1 to lookup a serial number, which could be on one of three sheets;
LE(A)2029A
LE(A)2029B
LE(A)2029D

it can only exist on one of those three sheets, so no duplicates, and must return an exact match.

so basically, its a vlookup over 3 sheets.

It works but is horribly messy.

Any help is appreciated.

Kind regards,

Coops

 

[AlphaFrog]

Try this...

=IFERROR(VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0),

IFERROR(VLOOKUP($R$7,'LE(A)2029B'!$A$17:$S$25000,3,0),

IFERROR(VLOOKUP($R$7,'LE(A)2029D'!A17:$S$25000,3,0),"No Match")))​

 

[xlxllxlx]

iferror(VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0),iferror(VLOOKUP($R$7,'LE(A)2029B'!$A$17:$S$25000,3,0),VLOOKUP($R$7,'LE(A)2029D'!$A$17:$S$25000,3,0)))
This assumes the serial number existed in one sheet or another.

if serial number might not exist, or user key in wrong serial number to lookup, return "Serial number not found"
iferror(VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0),iferror(VLOOKUP($R$7,'LE(A)2029B'!$A$17:$S$25000,3,0),iferror(VLOOKUP($R$7,'LE(A)2029D'!$A$17:$S$25000,3,0),"Serial number not found")))

 

[Aladin Akyurek]

What is the result if successful - a text value?

 

[cooper645]

Fast responses, thank you,

Yes, it is a text value, I simply use the formula accross different cells to fill in a form based on the serial number of R7, and each varies by which column is returned. In the example column 3, but other cells will use a variation to return column 2,4,5, and so on

 

[Aladin Akyurek]

Fast responses, thank you,

Yes, it is a text value, I simply use the formula accross different cells to fill in a form based on the serial number of R7, and each varies by which column is returned. In the example column 3, but other cells will use a variation to return column 2,4,5, and so on

Just enter:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4},"not found",VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0),VLOOKUP($R$7,'LE(A)2029B'!$A$17:$S$25000,3,0),VLOOKUP($R$7,'LE(A)2029A'!$A$17:$S$25000,3,0)))

 

[cooper645]

Can you explain how that last one with the rept function works, in order to expand my knowledge and help me understand should I need to troubleshoot it.

Thanks in advance

 

[Aladin Akyurek]

Can you explain how that last one with the rept function works, in order to expand my knowledge and help me understand should I need to troubleshoot it.

Thanks in advance

1. CHOOSE constructs an array of results (result-array). This will be here something like:

{"not found",#N/A,"TX$3XT","#N/A")

2. LOOKUP ignores errors in the reference it's asked to look at (which is here a reference like the one shown in [1]) if it possibly can.

3. LOOKUP is given a string 255 z's (REPT means here REPEAT z 255 times). Trying to locate this string in the reference, LOOKUP lands on the last text value (here: TX$3XT) it comes across in the reference and returns that value.

 

[cooper645]

Brilliant. Thank you everyone