2 questions on vlookup

[wwbwb]

1. Is it possible to have vlookup grab information from the left instead of the right? If so, how? ex. the column it looks up is b and pulls from c, d, e, etc. I would like it to pull from a, without having to change the chart to work the way it normally does.

2. When using vlookup to find an exact match, how can I have it display nothing when it can't find anything? Currently it displays #n/a. I need it to show a blank.

 

[MDuff]

Try these they have what you are looking for


http://www.mrexcel.com/tip021.shtml

http://www.mrexcel.com/board2/viewtopic.php?t=72428&highlight=

Let me know if you need any help with them

 

[ctrlaltdel]

Hi,

For your first question, I think you are going to have to move the column over to the right of your lookup column. I can't imagine how else to do it.

For the second question, try replacing the formula

=vlookup()

with

=if(isna(vlookup()),"",vlookup())

That way if #N/A would have appeared, you get the fake empty cell instead.

 

[jimboy]

See formula in E1 & E2

Book1
	A	B	C	D	E
1	10	North	A		10
2	20	South	B		A
3	30	East	C
4	40	West	D
Sheet5

 

[jimboy]

=if(isna(vlookup()),"",vlookup())

isna is better than iserror, use instead of my formula...

 

[MDuff]

=if(isna(vlookup()),"",vlookup())

isna is better than iserror, use instead of my formula...

Can i ask why is na is better than iserror?

thanks

 

[wwbwb]

Thanks. They both work. ray:

 

[jimboy]

=if(isna(vlookup()),"",vlookup())

isna is better than iserror, use instead of my formula...

Can i ask why is na is better than iserror?
thanks

This is written by NateO

To tack-on to Paddy's response, iserror() is just inefficient. Typically, I see this function paired with lookup(), match() or if(). All of which are expensive in their own right. Conceptuallly, it has to be much more efficient to test the error at hand, #n/a or div/o specifically than a function that tests for a host of potential application errors.

from this link;

http://www.mrexcel.com/board2/viewtopic.php?t=66812&postdays=0&postorder=asc&start=10

 

[just_jon]

1. Is it possible to have vlookup grab information from the left instead of the right? If so, how? ex. the column it looks up is b and pulls from c, d, e, etc. I would like it to pull from a, without having to change the chart to work the way it normally does.

2. When using vlookup to find an exact match, how can I have it display nothing when it can't find anything? Currently it displays #n/a. I need it to show a blank.

1] You can substitute INDEX with MATCH for looking up things from right to left. The following VLOOKUP formula -

=VLOOKUP(A1,D20:F100,3,0)

could also be achieved by -

=INDEX(F20:F100,MATCH(A1,D20:D100,0),0)

2] For a listing of ways around error messages in formulas, see -- http://216.92.17.166/board2/viewtopic.php?t=62102

 

[MDuff]

=if(isna(vlookup()),"",vlookup())

isna is better than iserror, use instead of my formula...

Can i ask why is na is better than iserror?
thanks

This is written by NateO

To tack-on to Paddy's response, iserror() is just inefficient. Typically, I see this function paired with lookup(), match() or if(). All of which are expensive in their own right. Conceptuallly, it has to be much more efficient to test the error at hand, #n/a or div/o specifically than a function that tests for a host of potential application errors.

from this link;

http://www.mrexcel.com/board2/viewtopic.php?t=66812&postdays=0&postorder=asc&start=10

Thanks I will start changeing some of my forumals then...