Hi,
I am doing a vlookup to a different worksheet to return a value based on a unique key. However I now want to expand the vlookup to do the following - if the original vlookup returns a blank or a #N/A I want the formula to do a second vloopup to a different column in a different worksheet and return that value (if it exists).
Can anyone help?
G'day and welcome to the forum Mark,
Try this
=IF(ISNA(VLOOKUP(First info)),VLOOKUP(Second info),VLOOKUP(First info))
Cheers
RC
Last edited by ratcat; 04-24-2010 at 06:36 AM. Reason: Missed a bracket in the formula
Have I made you happy ??? If yes, please make me happy by pressing theAdd Reputation button in my post.
Please don't forget to do the same to other contributors of this forum.
ThanksI don't void confusion, I create it
Doubling up the Vlookup for the IF condition is rather expensive. Also, since you want to test for both #NA and blank returns, you'll probably be looking for something along the lines of this.
Vlookup will not return a blank. If the found cell is blank, vlookup will return a zero, hence the test for =0
Please Login or Register to view this content.
Hi,
thank you for the quick responses. I have tried those but get a messgae saying too many arguements. I was also getting this error when i tried to construct the formulas myself. Can you please have another look with the attached spreadsheet.
In worksheet Service No
-if the lookup in column C returns a value that is not blank, #N/A or zero leave the result in the cell
- if the lookup returns a value that is blank, #N/A or zero do a second lookup using Cell B as the reference and this time lookup workbook "Warranty" using column A as the reference and column M in the array (Col 13) and return this value to worksheet "Service No" column c.
Can you please help?
Dissecting your formula
=VLOOKUP('Service No'!B:B,List!A:B,2,FALSE)
The first argument of the Vlookup should be a either a value or a reference to a single cell, not a whole column.
Applying the formula I posted above to you attached workbook: in C2 and copied down
cheersPlease Login or Register to view this content.
Thanks. That works well. Thank you very much.
However, I now have the following situation. Worksheet "List" will not always have the full set of service numbers which are listed in column A1. in the new attachment I have removed row values for A5,A11, A14, A16 and the formula will generate an N/A.
In the worksheet "Warranty" all the service numbers will be listed in column A. Where system name is not listed in worksheet "List" a value will be entered in worksheet "Warranty" in Column M in the corresponding row.
Is it possible to do a lookup from worksheet "Service No" in column c to do the following
-look up worksheet "List" and return the value - service tag / service no is the unique key
-If the above lookup detects an N/A, blank or zero then do a lookup to worksheet "Warranty" and retrun the value there.
OK, how about this
Please Login or Register to view this content.
Hi,
thanks for that. it works perfect now. I have to construct a few more of these types of lookups for other columns. Could you briefly explain the constructon of each section in the brackets at a high level. The v lookups dont need explainig just how they are nested.
if you had the time (high level only) i would really appreciate it. Anyway - the formula works a treat so thanks again.
let's break it down ...
Please Login or Register to view this content.
Last edited by teylyn; 04-27-2010 at 08:22 AM.
Teylyn,
many thanks for your help with this. I appreacte the breakdown as well.
Thanks again.
Last edited by Mark O'Loughlin; 04-27-2010 at 08:43 AM.
How to get next matching value with the help of Vloopup if first returns null or Zero and fill in the other column value
As in the below example
I have set of data in colA and colB which has applciation name and status
colg and colh has a status of the dependency - based on the colG and colH value we need to fill on the colA and colB value.
Currently it is take first cell value, where as it require next value which is not null or blank.
I have set of data
colA colB
StartApp RELEASED
WaterApp
WoodApp
colG Colh
StartApp RELEASED
WaterApp
WaterApp IMPACTED
WoodApp
WoodApp IMPACTED
Any help high appreciated
vemanrk,
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks