Need help with VLOOKUP to pull data from another worksheet

D

Daburger

New Member
Joined
Jul 15, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi all,

I need assistance with a VLOOKUP formula or if you know of another formula that can do the job, that would be great.

I have a workbook with two worksheets. sheet1 and sheet2
Both worksheets each have one table. sheet1 has table1 and sheet2 has table2
Both tables have different data and different columns, except for the first column (Column1).
This first column for both sheets consists of numbers (ie. 1000, 1001, 1002, 1034,1089, 1634, etc) that are in the same format.
However, the data in the first column will not be in the same order AND one table might have more data over the other.

With that in mind, what I am trying to do is as follows (using the tables displayed below):
Create a formula that goes at the end of the table on sheet1 (for example: cell F2), which then looks at the cell on the same row from the first column (A2 - this has the data 1010). It then matches the data (1010) with column1 sheet2 (in this example - cell A5). If the data matches, it then looks for a specific column of my choosing to the right (for this example, column4) and displays that data from the same row (D5 which shows the word 'green').

So back on sheet1 where the formula was entered, it should display the word 'green'.
If it cannot find a cell in the first column which matches the same data, it can either show as blank or N/A. not too fussed.

So to summarize the above:
1. The formula is to be entered in cell F2 of sheet1.
2. Cell A2 from sheet1 matches cell A5 from sheet2.
3. Since it matches, I want the formula to look for column4 and display the data that is from the same row (which is cell d5 and shows 'green').

Sheet1:
Book1.xlsx
ABCDEF
1Column1Column2Column3Column4Column6Column7 (formula goes in this column)
21010
31013
41019
51029
61096
Sheet1


Sheet2:
Book1.xlsx
ABCD
1Column1Column2Column4Column5
21001red
31002red
41003red
51010green
61008green
71012red
81013yellow
91016green
101029yellow
111030yellow
Sheet2


I appreciate any help that can be provided! If anything is unclear, please let me know and I will do my best to further explain.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
What about ..

Daburger.xlsm
ABCDEF
1Column1Column2Column3Column4Column6Column7 (formula goes in this column)
21010green
31013yellow
41019 
51029yellow
61096 
Sheet1
Cell Formulas
RangeFormula
F2:F6F2=IFNA(VLOOKUP([@Column1],Table2,3,0),"")
 
Upvote 0
Solution
Peter_SSs said:
What about ..

Daburger.xlsm
ABCDEF
1Column1Column2Column3Column4Column6Column7 (formula goes in this column)
21010green
31013yellow
41019 
51029yellow
61096 
Sheet1
Cell Formulas
RangeFormula
F2:F6F2=IFNA(VLOOKUP([@Column1],Table2,3,0),"")
Click to expand...

Thanks Peter,
Your formula worked on my test sheets (sheet1 and sheet2), however on the file I am actually wanting to do this to, I just get a blank cell as a result.
Unfortunately, I am unable to share this data for privacy reasons (ie. work). I checked again to make sure that the columns matched and that there was data there to display, however it would still show a blank cell.


Each row has data so no matter what number I replaced the 3 with, it should have shown something. I also renamed the columns and tables in my file to match your formula and had the same result. I suspect it's something to do with my file/tables unless you might know why it's happening?

Thanks again
 
Upvote 0
Daburger said:
Thanks Peter,
Your formula worked on my test sheets (sheet1 and sheet2), however on the file I am actually wanting to do this to, I just get a blank cell as a result.
Unfortunately, I am unable to share this data for privacy reasons (ie. work). I checked again to make sure that the columns matched and that there was data there to display, however it would still show a blank cell.


Each row has data so no matter what number I replaced the 3 with, it should have shown something. I also renamed the columns and tables in my file to match your formula and had the same result. I suspect it's something to do with my file/tables unless you might know why it's happening?

Thanks again
Click to expand...

NOTE:
I did change Table2 to table1 and it works and picks up data entered in which even column number I select etc. So the formula works.
So for some reason, it seems as though it is not picking up my other table on the other sheet, even though it finds it as I see it pop up when typing the formula in.
 
Upvote 0
Hard to tell without being able to see anything. I'm also now unsure of sheet names and table names.
Referring back to my sample though, the numbers being looked up must be in the very first column of the table in Sheet2. If they are not in the very first column then a different formula will be required.

If you need further help please carefully answer ..
  1. What is the name of the table that the formula is being placed in?
  2. What is the name of the worksheet that table is in?
  3. In that same table, what is the exact heading of the column that contains the numbers to be looked up?

  4. What is the name of the other table - the one that already has the colours in?
  5. What is the name of the worksheet that table is in?
  6. What is the exact heading of the table column containing the numbers being looked up?
  7. Counting from the left, which column number of the table contains those numbers being looked up?
  8. What is the exact name of the table column containing the existing colours?
  9. Counting from the left, which column number of the table contains those colours that will be returned by the formula?
 
Upvote 0
Daburger said:
NOTE:
I did change Table2 to table1 and it works and picks up data entered in which even column number I select etc. So the formula works.
So for some reason, it seems as though it is not picking up my other table on the other sheet, even though it finds it as I see it pop up when typing the formula in.
Click to expand...
Sorry for so many comments:
Further note, I also tried using =VLOOKUP([@Coulmn1],table2,3,0) and I end up with #N/A which is basically the same outcome I believe.
 
Upvote 0
Peter_SSs said:
Hard to tell without being able to see anything. I'm also now unsure of sheet names and table names.
Referring back to my sample though, the numbers being looked up must be in the very first column of the table in Sheet2. If they are not in the very first column then a different formula will be required.

If you need further help please carefully answer ..
  1. What is the name of the table that the formula is being placed in?
  2. What is the name of the worksheet that table is in?
  3. In that same table, what is the exact heading of the column that contains the numbers to be looked up?

  4. What is the name of the other table - the one that already has the colours in?
  5. What is the name of the worksheet that table is in?
  6. What is the exact heading of the table column containing the numbers being looked up?
  7. Counting from the left, which column number of the table contains those numbers being looked up?
  8. What is the exact name of the table column containing the existing colours?
  9. Counting from the left, which column number of the table contains those colours that will be returned by the formula?
Click to expand...
  1. What is the name of the table that the formula is being placed in? Live
  2. What is the name of the worksheet that table is in? Live_Bucket
  3. In that same table, what is the exact heading of the column that contains the numbers to be looked up? SO
  4. What is the name of the other table - the one that already has the colours in? RAW
  5. What is the name of the worksheet that table is in? RAW_Data
  6. What is the exact heading of the table column containing the numbers being looked up? SO (both tables have the same column name)
  7. Counting from the left, which column number of the table contains those numbers being looked up? 1
  8. What is the exact name of the table column containing the existing colours? Sub-Series
  9. Counting from the left, which column number of the table contains those colours that will be returned by the formula? 29
 
Upvote 0
Thanks for the clear responses. I think that I have now replicated that set-up and have this formula in the 'Live' table and it is working

Excel Formula: 
=IFNA(VLOOKUP([@SO],RAW,29,0),"")

If you have that formula and it is returning all blanks then almost certainly the problem is that column 'SO' in one table contains actual numbers and column 'SO' in the other table contains text values. (There could be other reasons too though)
Try putting these two formula in blank cells and see if you get different results like this (I have now changed my data so that one table has numbers and one has text)

Daburger.xlsm
B
14FALSE
15TRUE
Live_Bucket
Cell Formulas
RangeFormula
B14B14=ISNUMBER(INDEX(Live[SO],1))
B15B15=ISNUMBER(INDEX(RAW[SO],1))


If you get a TRUE and a FALSE then please advise which table does have the actual numerical values.
 
Upvote 0
Peter_SSs said:
Thanks for the clear responses. I think that I have now replicated that set-up and have this formula in the 'Live' table and it is working

Excel Formula: 
=IFNA(VLOOKUP([@SO],RAW,29,0),"")

If you have that formula and it is returning all blanks then almost certainly the problem is that column 'SO' in one table contains actual numbers and column 'SO' in the other table contains text values. (There could be other reasons too though)
Try putting these two formula in blank cells and see if you get different results like this (I have now changed my data so that one table has numbers and one has text)

Daburger.xlsm
B
14FALSE
15TRUE
Live_Bucket
Cell Formulas
RangeFormula
B14B14=ISNUMBER(INDEX(Live[SO],1))
B15B15=ISNUMBER(INDEX(RAW[SO],1))


If you get a TRUE and a FALSE then please advise which table does have the actual numerical values.
Click to expand...

Thanks Peter,

I feel terribly silly right now:
"Try putting these two formula in blank cells and see if you get different results like this (I have now changed my data so that one table has numbers and one has text)"

For column1 in the other table, the numbers were not formatted as a number. Once converted, boom! worked like a charm. Tried the original formula you suggested, also working!

I really appreciate your help and patience! You have saved me many many hours of hair pulling and money on buying regain hair cream for men!! :ROFLMAO:
 
Upvote 0
Glad you got it sorted.

If the process has to be repeated then there is no need to convert the numbers in the table if you don't want to as a formula adjustment can handle it.
So if Live has numbers and RAW has text then you could use this formula.
Excel Formula: 
=IFNA(VLOOKUP([@SO]&"",RAW,29,0),"")

The formula below may be even better though as it would not require adjustment of the 29 in the above formula if columns are inserted/removed in the RAW table. It would also work if 'SO' was not the first column in RAW and even if 'Sub-Series' happened to be on the left of 'SO'.
Additionally this formula is not forced to recalculate if any value anywhere in RAW is altered like the above formula.
Finally it is a little more efficient than VLOOKUP anyway and does not require the IFNA.

VBA Code: 
=XLOOKUP([@SO]&"",RAW[SO],RAW[Sub-Series],"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VLOOKUP using two criteria
Replies
3
Views
590
etaf
etaf
P
  • Solved
VBA to look down column and over write cell with text from list
Replies
5
Views
86
Peter_SSs
Peter_SSs
N
  • Question Question
Match and show the data from 2 sheets
Replies
3
Views
195
kvsrinivasamurthy
K
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
708
Micron
M
T
  • Question Question
Reordering data duplicated across two worksheets?
Replies
1
Views
158
dreid1011
dreid1011

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top