Hello new friends
I need to know how to use the vlookup fomula to pull data from multiple columns rather than just one so that when data is returned, data will populate multiple cells in a given row. Any ideas?
Hello new friends
I need to know how to use the vlookup fomula to pull data from multiple columns rather than just one so that when data is returned, data will populate multiple cells in a given row. Any ideas?
can you upload a sample document and explain which rows you want to collate / what you actually want to happen so we can help please.
Please add Rep if I've helped
The third parameter of the VLOOKUP function determines which row in the lookup table you want to get the data from, e.g.:
=VLOOKUP($A2,Sheet2!$A$1:$G$100,3,0)
where the data is returned from the 3rd column (C) of the table A1:G100 on Sheet2.
However, you can make this variable, so that as the formula is copied across the column number adjusts automatically, like this:
=VLOOKUP($A2,Sheet2!$A$1:$G$100,COLUMNS($A:C),0)
Here the term COLUMNS($A:C) initially returns 3, but as the formula is copied across this term will become COLUMNS($A:D), then COLUMNS($A:E), thus returning 4 then 5, and so on.
Hope this helps.
Pete
Pete_UK
Im using the formula buts its still only populating one cell.
SampleWorkbook.xls
Here is a sample workbook. I want to make it so that when I select branch manager on the user interface, it will populate cells A10 to C10 on the same page. The cells would contain the three names following the branch manager on the control interface.
Put this in A10:
Formula:![]()
=VLOOKUP($A$1,ControlInterface!$A:$D,COLUMNS($A:B),0)
then copy into B10 and C10.
Use the drop-down in A1 to see the numbers change automatically.
Hope this helps.
Pete
vlookup syntax is...
=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-answer-you-want,FALSE) FALSE is for finding an exact match
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks