Ugly Vlookup

[Corticus]

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

 

[Aladin Akyurek]

How about this?

VlookupExamples.xls
	A	B	C	D	E	F
1	Lookupvalue	Result	HideThisColumn		Masterrefersto:F2:G7
2	1	a	a		1	a
3	2		#N/A		3	b
4	3	b	b		4	c
5	4	c	c		5	d
6					6	e
7					7	f
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]

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]

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