Vlookup, when found, lookup value in diff column, based on value return X or blank

[skeylargo]

As I was typing the title it seemed very complicated, hopefully below info will clarify it

• Each record has unique Reg#
• Given the data below, my formula in Destination sheet in cell B1 would return "X" if Packing Rating="Sat." otherwise return blank.
• Same type of Formula in Destination sheet in Cell C1 would return blank
• I need to populate a Destination spreadsheet that has different columns for different ratings types.

Source
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Reg#[/TD]
[TD="align: center"]Packing Rating[/TD]
[TD="align: center"]Crew Rating[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]Sat.[/TD]
[TD="align: center"]Unsat.[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]124[/TD]
[TD="align: center"]Usat.[/TD]
[TD="align: center"]N/A[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]135[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]Sat.[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]134[/TD]
[TD="align: center"]Unsat.[/TD]
[TD="align: center"]Sat.[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]154[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]Unsat.[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]145[/TD]
[TD="align: center"]Sat.[/TD]
[TD="align: center"]N/A[/TD]
[/TR]
</tbody>[/TABLE]

Destination
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reg#[/TD]
[TD]Packing Rating[/TD]
[TD]Crew Rating[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]124[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]135[/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]134[/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]154[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]145[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

I did something a lot of simpler with just VLOOKUP but this seems like it needs to be nested IF with possibly other functions and this is beyond my ability.

Thank you for any assistance you can provide.

 

[dnicholsby]

=IF(VLOOKUP($B17,$A$2:$C$7,2,FALSE)="Sat.","X","")

Goes into your first row

=IF(VLOOKUP($B17,$A$2:$C$7,3,FALSE)="Sat.","X","")

Goes into your second row

Where A2:C7 references your source table and B17 references the first row in your destination table.

 

[skeylargo]

Thank you, but I guess I wasn't clear enough, sorry...

The data in both tables in real life will not match so nicely.
First I need to find the matching Reg# record in my Source sheet and than look up the value in the rating column and apply the criteria provided.
What I was hung up on was how to find the correct record first and then check for the data in the same rows in different columns.

 

[dnicholsby]

Thank you, but I guess I wasn't clear enough, sorry...

The data in both tables in real life will not match so nicely.
First I need to find the matching Reg# record in my Source sheet and than look up the value in the rating column and apply the criteria provided.
What I was hung up on was how to find the correct record first and then check for the data in the same rows in different columns.

I'm sorry but i don't understand what you need exactly?

The data is your 2nd and third row, what is that and what are you trying to achieve with your destination table?

 

[skeylargo]

OK, So my Destination sheet really looks more like this:

[/URL][/IMG]

So a user would type in Reg# and each cell would have a formula that pulls information from the Source sheet. I have no problem using Vlookup for other fields like Account or Driver etc but not sure how to do it for the rating part.
This is what I actually have in my test worksheet for Date field (My Reg# is entered in C9): =VLOOKUP(C9,Sheet2!1:500,4,FALSE)

In order to fill those X's, first I need to find a row with the record 1234 in the Source sheet and than go to the column that has rating in it and use your Vlookup part. So I need to add another vlookup ??? to first to find the correct record and combine it with the part you already provided?

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Reg#[/TD]
[TD]Account[/TD]
[TD]Driver[/TD]
[TD]Date[/TD]
[TD]Proper uniform[/TD]
[TD]Proper Grooming[/TD]
[TD]Proper attitude[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD]ABC[/TD]
[TD]Al[/TD]
[TD]6/1/18[/TD]
[TD]Unsat[/TD]
[TD]Sat[/TD]
[TD]Unsat[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]XYZ[/TD]
[TD]Bob[/TD]
[TD]6/1/18[/TD]
[TD]Sat[/TD]
[TD]Sat[/TD]
[TD]Unsat[/TD]
[/TR]
[TR]
[TD]1222[/TD]
[TD]ABC[/TD]
[TD]Joe[/TD]
[TD]5/2/18[/TD]
[TD]Unsat[/TD]
[TD]Unsat[/TD]
[TD]Unsat[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]AAA[/TD]
[TD]Frank[/TD]
[TD]5/5/18[/TD]
[TD]Unsat[/TD]
[TD]Sat[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

I do hope this makes more sense now ....

 

[skeylargo]

Duuuuh, (slap myself on a forehead).

I missed a comma, excel asked to correct the formula and it remove another comma and that's why it gave me a False result.

With correct syntax, it works GREAT!

Thank you!!!