# Ugly Vlookup

#### Corticus

##### Well-known Member
Hi all,

This formula works, but is very ugly, and slowing down my sheet a bit. Just for the sake of academia, is there a better way? BTW, the formula is designed to do a simple vlookup, but allow for different formats(text/number) without returning errors:

=IF(ISERROR(VLOOKUP(TEXT(AE2,"#########"),Master,13,0))=TRUE,IF(ISERROR(VLOOKUP(AE2+0,Master,13,0))=TRUE,IF(ISERROR(VLOOKUP(AE2,Master,13,0))=TRUE,"",VLOOKUP(AE2,Master,13,0)),VLOOKUP(AE2+0,Master,13,0)),(VLOOKUP(TEXT(AE2,"#########"),Master,13,0)))

Thanks,
Corticus
This message was edited by Corticus on 2002-10-25 15:28

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

##### MrExcel MVP
VlookupExamples.xls
ABCDEF
1LookupvalueResultHideThisColumnMasterrefersto:F2:G7
21aa1a
32 #N/A3b
43bb4c
54cc5d
66e
77f
8
Example2

This uses a smaller table, but the idea applies to your larger situation...

The essential formula is:

=IF(ISNUMBER(MATCH(A2,INDEX(Master,0,1),0)),VLOOKUP(A2,Master,2,0),VLOOKUP(A2&"",Master,2,0))

Notice the space versus time trade off, that is, spending additional cells in order not to loose speed.

#### Dave Patton

##### Well-known Member
On 2002-10-25 15:27, Corticus wrote:
Hi all,

This formula works, but is very ugly, and slowing down my sheet a bit. Just for the sake of academia, is there a better way? BTW, the formula is designed to do a simple vlookup, but allow for different formats(text/number) without returning errors:

=IF(ISERROR(VLOOKUP(TEXT(AE2,"#########"),Master,13,0))=TRUE,IF(ISERROR(VLOOKUP(AE2+0,Master,13,0))=TRUE,IF(ISERROR(VLOOKUP(AE2,Master,13,0))=TRUE,"",VLOOKUP(AE2,Master,13,0)),VLOOKUP(AE2+0,Master,13,0)),(VLOOKUP(TEXT(AE2,"#########"),Master,13,0)))

Thanks,
Corticus
This message was edited by Corticus on 2002-10-25 15:28

With the left column of your lookuptable
a TEXT field. It is very easy to make the left column a Text field.

=IF(ISNA(SETV(VLOOKUP(A2&"",rdata,3,0))),"",GETV())

http://longre.free.fr/english/index.html

The GETV means the formula has to lookup the value only once versus multiple times.

A2&"" means a number in A2 will be read as TEXT and Compared to the TEXT in the LookupTable.
This message was edited by Dave patton on 2002-10-25 16:29

#### Corticus

##### Well-known Member

It looks perfect(and much faster), hope you didn't read the post that was just here, I was a little retarded for a minute!

I didn't know that using cells to test criteria instead of embedding it in the original formula would help that much. I actually thought it was bad to not fit it all in one formula. I will use this method more often.

Thanks,
Corticus

note:
after furthur testing, my sheet calculates about twice as fast. Nice!

Thanks Dave, too. I am trying to avoid using add-ins whenever possible because my sheets get distributed and not everybody has the MSOffice disks, so I can't automate turning on the add-ins.
This message was edited by Corticus on 2002-10-25 16:36

Replies
0
Views
239
Replies
3
Views
221
Replies
10
Views
172
Replies
0
Views
143
Replies
26
Views
1K

1,181,532
Messages
5,930,453
Members
436,740
Latest member
Ogidi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back