Nest VLOOKUp with ISBLANK to

[Negger]

Previous Post:
http://www.mrexcel.com/forum/excel-questions/714779-importing-data-access-database.html
I solved the previous post by using VLOOKUP :
=IFERROR(VLOOKUP(A10,Main_Table, 3, FALSE)," ")
Having exported the required fields from Access to Excel, I saved them as a Table to a second worksheet within my workbook. I then set a data validation drop down as the first column of my worksheet which selected the appropriate records from the Table. The VLOOKUP formual then auto filled the rest of the cells (simply changing the column number "3" to the appropriate column.
However, I now need to selectively concatanate two further columns, House Name & House Number, whereby if an address has a house number, it returns that number, if not, it returns the house name. I can do this within the same worksheet by using :
=IF((ISBLANK(AQ9)),AO9,AQ9) where Column AQ is the house number and Column AO is the house name. However I need to select the correct row as in VLOOKUP above, so presumably I need to nest the two functions ?
Can anyone please show me the correct syntax to combine these two functions so that I reference the correct row number ?
Many Thanks
Access/Excel 2007
Windows XP

 

[Robert Mika]

=IFERROR(IF((ISBLANK(VLOOKUP(A10,main_table, 3, FALSE))),VLOOKUP(A10,main_table, 2, FALSE),VLOOKUP(A10,main_table, 3, FALSE)),"")

 

[Negger]

=IFERROR(IF((ISBLANK(VLOOKUP(A10,main_table, 3, FALSE))),VLOOKUP(A10,main_table, 2, FALSE),VLOOKUP(A10,main_table, 3, FALSE)),"")

Robert, Many thanks for working that one out, does exactly what I wanted. I had been trying to nest ISBLANK within VLOOKUP. Is there a KB that explains the logic of working these formula out, rather than just trying trial and error ?
Thank you Again.

 

[Robert Mika]

Robert, Many thanks for working that one out, does exactly what I wanted. I had been trying to nest ISBLANK within VLOOKUP. Is there a KB that explains the logic of working these formula out, rather than just trying trial and error ?
Thank you Again.

The logic in my formula or logic in the one you want to deliver.
If mine:
ISBLANK is a logical function delivering TRUE/FALSE.
So if Your return from first VLOOKUP is blank it will go to the second option(the FALSE part of IF) if is non blank it will deliver output from the first formula.(the TRUE statment of IF)
IFERROR wil return ""(empty cell) if there is #N/A error.

You can not nest ISBLANK in VLOOKUP.

 

[Negger]

HI Robert, The logic in your formula ! I think it better that I simply bow to your greater intellect.
I'm now going to move on to see if I can shorten the data validation list by means of a filter. The dvl has around 15 numerical sub-sets.
Once again Many Thanks.

 

[Robert Mika]

HI Robert, The logic in your formula ! I think it better that I simply bow to your greater intellect.
I'm now going to move on to see if I can shorten the data validation list by means of a filter. The dvl has around 15 numerical sub-sets.
Once again Many Thanks.

You are welcome.
And if you need help with your second question please open new topic.

 

[Obwan]

You are welcome.
And if you need help with your second question please open new topic.

I know this is an old post but it is exactly what I needed. Thank Robert.