Ugly Vlookup

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
How about this?
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
Joined
Feb 15, 2002
Messages
4,976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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())

which requires the morefunc.xll add-in, downloadable from

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
Joined
Apr 30, 2002
Messages
1,579
Thanks Aladin,

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.

You're awesome, Aladin!

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
 

Forum statistics

Threads
1,144,768
Messages
5,726,179
Members
422,660
Latest member
mrsteele

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top