Look up a value in a range and return the value of the first column of the row with the match

[mikalal]

Hello:

I am trying to reconcile our IT Assets.

The workbook has 3 worksheets
Worksheet 1 looks like:

Asset Tag
1001
1002
1003
1....

Worksheet 2 looks like

Name/Location   Desktop  Laptop Monitor1 Monitor2 Monitor3
John Smith                            1003   1004       1005
Library                   1001                   1006

Worksheet 3 looks like

Name/Location   CF19  Laptop Monitor1 Monitor2 Monitor3
Jim                         1002              1007
Jean                       1009               1008

I would like Worksheet 1 to search the asset tag in the two other worksheets, and if a match is found, then return the name/location to the cell next to it. If no match is found, then the row in worksheet 1 should be highlighted to a color e.g Red:

Asset Tag  Name/Location
1001          Library
1002          Jim
1003          John Smith
50001       Not Found (and the row should turn red in color)

I tried using INDEX and MATCH, but only get #NA in cell B2 of Worksheet 1. I used:
{=INDEX($A$1:$A$262,MATCH(A1,NamedRange,0))}

Help!

 

[azumi]

Try to provide your sample workbook to avoid retyping the data

Regards

 

[Vikas_Gautam]

Got the solution...

Check the formula..

=INDEX($B$2:$B$3,MAX((N($C$2:$E$3=N2)*ROW(1:2))))
it is for one one sheet..

check the attachment..

Don't forget to click *

 

[mikalal]

@azumi
Thanks for the suggestion.. I have uploaded the sample workbook.

I need the Name/location column on the 1st sheet to be populated from the other sheets, depending on what name/location/vehicle has the asset.
If the asset cannot be found on the other sheets, then the row for that asset tag (on the "Asset Tag Number" sheet) should turn red.

Also, another twist...If the asset tag number on sheets 2, 3, or 4 does not exist on sheet 1, then that asset tag (cell on WS2/WS3/WS4) should be highlighted.


@Vikas_Gautam
I am not sure what is being done there (I am too much of a noob!).. Maybe this uploaded workbook will clarify what I need.

 

[Haseeb A]

Hello Mikalal,

See the attached. I have converted first sheet data to Table. SO this will adjust accordingly when Add/Delete data.