I have 2 separate sheets: 1 with data for 1 data type (STM) and one master file with multiple data types (ELA, STM). I have a vlookup FROM the first file to the master, which I need to fine tune, as I only want to lookup on records for STM. In some cases there is data for a given ID for both types, and the correct row is not always found.
Here is the existing vlookup:
=VLOOKUP(F5,[9793_MasterFile.xlsx]Sheet1!$D:$K,8,FALSE)
I need to perform this only for records where Subject (column B) = "STM".
I am looking online, but most of the conditional forms of vlookup examples I am finding are with the conditions on the file searching from, not for the file being searched. Anyone know how to accomplish this?
=if([bcolumn]="STM",vlookup(cell, range,2,false),"")
In your first sheet, assume the existing identifier that you are using is in column C. The new column can be created with a formula of
=C1&"STM"
Copy/paste down all rows
In the master sheet, assume the existing identifier is in Col C and the STM/ELA distinction is in Col D. The formula in the new column would be
=C1&D1
Copy paste down all rows.
The new columns will serve as the common identifier that can be used as the reference column for the vlookup
In your first sheet, assume the existing identifier that you are using is in column C. The new column can be created with a formula of
=C1&"STM"
Copy/paste down all rows
In the master sheet, assume the existing identifier is in Col C and the STM/ELA distinction is in Col D. The formula in the new column would be
=C1&D1
Copy paste down all rows.
The new columns will serve as the common identifier that can be used as the reference column for the vlookup
Is there another way without altering the data, including adding columns?
Quote:
Is add a column at the beginning of each file to concatenation the identifier you are currently using with the letters STM.
In your first sheet, assume the existing identifier that you are using is in column C. The new column can be created with a formula of
=C1&"STM"
Copy/paste down all rows
In the master sheet, assume the existing identifier is in Col C and the STM/ELA distinction is in Col D. The formula in the new column would be
=C1&D1
Copy paste down all rows.
The new columns will serve as the common identifier that can be used as the reference column for the vlookup
Thanks. this should work. I should have thought of this as a workaround. But, I was hoping not to have to manipulate the data or format of the master file.
Is there another way without altering the data, including adding columns?
Hokie's method would work fine.
Sheet1 is the tab you are matching against, could be named anything. B is what you are returning. So say I was matching an ID, in sheet 1 I would just add the ID to column A and fill column B with an X or whatever, so it will return an X whenever there is a match.
Just throwing it out there in case it helps.
F5 = your original value you were looking up
B5 = replace with whichever cell on the file you are bringing the master data back to that has the STM value
master K is the cell you are bringing back
When you enter the formula in the cell you are putting it in, you have to hit CTRL+SHFT+ENTER to set the array. It will copy down fine after that.
my 3:18 will solve for this. If you don't have the STM value on the file you are trying to return data to, you can hardcode it in the formula where I have B5.
Quote:
The vlookup is matching on ID between my data file and the master file. However, there are 2 rec types on the master file (ELA, STM). I want the vlookup to return data only for STM, but for the match on ID. The vlookup is easy; I'm not sure the best way to add the criteria of a specific rectype on the master file.
my 3:18 will solve for this. If you don't have the STM value on the file you are trying to return data to, you can hardcode it in the formula where I have B5.
Quote:
In comment 13504054 Matt M. said:
Quote:
The vlookup is matching on ID between my data file and the master file. However, there are 2 rec types on the master file (ELA, STM). I want the vlookup to return data only for STM, but for the match on ID. The vlookup is easy; I'm not sure the best way to add the criteria of a specific rectype on the master file.
my 3:18 will solve for this. If you don't have the STM value on the file you are trying to return data to, you can hardcode it in the formula where I have B5.
This does work. I did have STM on the working file. Thank you!