Need help with VLOOKUP to pull data from another worksheet

[Daburger]

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
	A	B	C	D	E	F
1	Column1	Column2	Column3	Column4	Column6	Column7 (formula goes in this column)
2	1010
3	1013
4	1019
5	1029
6	1096
Sheet1

Sheet2:

Book1.xlsx
	A	B	C	D
1	Column1	Column2	Column4	Column5
2	1001		red
3	1002		red
4	1003		red
5	1010		green
6	1008		green
7	1012		red
8	1013		yellow
9	1016		green
10	1029		yellow
11	1030		yellow
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.

 

[Peter_SSs]

What about ..

Daburger.xlsm
	A	B	C	D	E	F
1	Column1	Column2	Column3	Column4	Column6	Column7 (formula goes in this column)
2	1010					green
3	1013					yellow
4	1019
5	1029					yellow
6	1096
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IFNA(VLOOKUP([@Column1],Table2,3,0),"")

 

[Daburger]

What about ..

Daburger.xlsm
	A	B	C	D	E	F
1	Column1	Column2	Column3	Column4	Column6	Column7 (formula goes in this column)
2	1010					green
3	1013					yellow
4	1019
5	1029					yellow
6	1096
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IFNA(VLOOKUP([@Column1],Table2,3,0),"")

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

 

[Daburger]

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

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.

 

[Peter_SSs]

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?

 

[Daburger]

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.

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.

 

[Daburger]

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?

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

 

[Peter_SSs]

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

=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
14	FALSE
15	TRUE
Live_Bucket

Cell Formulas
Range	Formula
B14	B14	=ISNUMBER(INDEX(Live[SO],1))
B15	B15	=ISNUMBER(INDEX(RAW[SO],1))

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

 

[Daburger]

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

=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
14	FALSE
15	TRUE
Live_Bucket

Cell Formulas
Range	Formula
B14	B14	=ISNUMBER(INDEX(Live[SO],1))
B15	B15	=ISNUMBER(INDEX(RAW[SO],1))

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

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!!

 

[Peter_SSs]

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.

=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.

=XLOOKUP([@SO]&"",RAW[SO],RAW[Sub-Series],"")