Keren has attended:
Excel Advanced course
Nesting Vlookup Functions
Hi, I need to pull in information from various sheets into a main Sheet within a workbook. I was wondering if there is way that I can use various VLOOKUP/nesting VLOOKUP functions to do this? For example =VLOOKUP(A1,Sheet1,Columns,FALSE),VLOOKUP(A1,Sheet2,Columns,FALSE),VLOOKUP(A1,Sheet3,Columns,FALSE) and so on. I have of course tried this formula and it was unsuccessful.
Each tab has different identifiers for the information available on that tab and I want to be able to pull the necessary information for different identifiers within the different sheets into the main sheet. Any advice would be greatly appreciated. Thanks. Keren
RE: Nesting Vlookup Functions
Hi Keren,
Thank you for using the forum to ask a question.
If you want to use A1 from Sheet 1, you need to write it as Sheet1!$A$1. The exclamation mark identifies the sheet, and the dollar signs fix the row and column. If you write it as =VLOOKUP(A1,Sheet1,Columns,FALSE) the extra comma between A1,Sheet1 will confused Excel.
=Vlookup(Sheet1!$A$1,table_array,column index, False)
The table array is the selection of data (columns) that you
want to lookup, including the headings. This will vary for each lookup you are doing, depending on the sheet. The table array is not always the entire set of data you have on the sheet.
The array starts with column 1 being the heading of the look up value, and will extend to the right to include the heading you want returned.
Try one Vlookup per sheet to see if adjusting the formula helped. Then I can explain Nesting if you require.
Kind regards
Richard Bailey
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Nesting Vlookup Functions
Hi Richard,
Thanks for your response. Apologies, I don't think I have explained myself well.
The workbook is made up of 7 sheets and one main sheet (e.g. Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5, Sheet 6 and then the Main Sheet).
I need to pull in the information from sheets 1-6 into the main sheet. Cell A1 is the identifier used to pull the information from the back sheets into the Main Sheet. I need excel to look up the identifier in cell A1 of the main sheet in Sheets 1-6. Hence the Formula and nesting question. I need excel to lookup the identifier in each worksheet within the workbook. I have tried simply:
=VLOOKUP(A1,'Sheet1'!$A$1:$AJ$274,29,FASLE),VLOOKUP(A1,'Sheet2'!$A$1:$AJ$274,29,FASLE),VLOOKUP(A1,'Sheet3'!$A$1:$AJ$274,29,FASLE).....
This has not worked. Any suggestions you can give would be greatly appreciated. Thanks. Keren
RE: Nesting Vlookup Functions
Hi Keren,
Thanks for clarifying, now I can see what you meant.
If you want to return more than 1 Vlookup into a single cell, use "&" ampersand rather than a "," comma.
=VLOOKUP(A1,'Sheet1'!$A$1:$AJ$274,29,FASLE)&VLOOKUP(A1,'Sheet2'!$A$1:$AJ$274,29,FASLE)...
It will supply all of the Vlookups from all sheets into that one cell.
Please let me know if that solution gets you any closer.
Kind regards
Richard Bailey
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Nesting Vlookup Functions
Hi Richard,
Thanks for coming back to me on that. Its now pulling in '#N/A'. It seems like if the identifier is found on one sheet and not on the other sheets it responds with '#N/A'? Is there a way that this can be rectified.
An 'IF(ISERROR...' function along with the VLOOKUP has been suggested. However, how can I work this into this formula? As in, if the information is not found in sheet 1, move on to sheet 2 and so forth pulling in the information from the Sheet that it is in?
Thanks
Keren
RE: Nesting Vlookup Functions
Hi Keren,
The IF(ISERROR helps you get past #NA but it doesn't Nest properly with your formula.
If the Lookup value was on every sheet but there was no data in the column you could change the condition of the Vlookup to True. This doesn't produce #NA error and therefore won't prevent the Lookup working for the other sheets.
=VLOOKUP(A1,'Sheet1'!$A$1:$AJ$274,29,TRUE)&VLOOKUP(A1,'Sheet2'!$A$1:$AJ$274,29,TRUE)
If you don't have the Lookup value on each sheet to start with, which seems to be your problem, there isn't an obvious solution using nested Vlookups. Vlookup does have a few limitations, sometimes you have to use a combination of other functions like Index and Match with IF(ISERROR)
If you would like us to spend time developing a custom formula, specific to the workbook you are using, we could discuss designing that.
Or
The work around is have a separate Vlookup for each sheet. Giving you 6 cells, each with data. Even if there is an #NA error, the rest will still calculate.
You can use the IF(ISERROR with the Vlookup to make the #NA appear as a space " "
=IF(ISERROR(VLOOKUP(A1,Sheet3!A1:B21,2,FALSE))," ",VLOOKUP(A1,Sheet3!A1:B21,2,FALSE))
Then use the CONCATENATE function to join all the separate data together into one cell if you wish.
=CONCATENATE(A2,” “,A3," ",A4," "A5)
Take out the " " if you want the data to combine without spaces.
Please let me know which option works best for you.
Kind regards
Richard Bailey
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
