Complicated vlookup

[JeremyLek]

Hi experts,


I need some helps on excel on the vlookup. I have 1 workbook with 2 worksheet.


In worksheet A, I was given a list of Feature ID with SW IDs

meaning 1 Feature ID = many SW IDs


Worksheet A

eg.

Feature ID|SW ID|SW ID|SW ID|

Feature_1 |RD_ENT_1|RD_NAVI_23|RD_ENT_2

Feature 2 ......


whereas in Worksheet B, I was given a list of SW ID but I need to find Feature ID based on SW ID provided.


eg

Worksheet B

Feature ID|SW ID|....

????? | RD_ENT_1

????? | RD_ENT_2

.....


Is there any macro of formula that allow me to compare the "SW ID" data in Worksheet B with a range of "SW ID"s in worksheet A?


Beside that, do vlookup formula be affected by the Capitalize data? because in Worksheet B, all data are small cap and in the Worksheet A is all large cap.


Please help me on that.

 

[srinidhi]

Hi,


Whenever you want to look up in multiple criteria you need use the index & match formula, as vlookup does not support multiple criteria match.


here goes the formula, adjust the values as per u r needs,


Index(result u want to display,MATCH('value to be matched',Range to look up for ,FALSE),1)


Its an array formula u need to shift+ctrl+enter


it is not case sensitive

 

[JeremyLek]

I tried Index with Match but this formula can only compare with 2 columns.


meaning I can search worksheet B's SW ID with Worksheet A 1st column SW ID but not the 2nd SW ID column.

 

[srinidhi]

no u can search with many column SW ID.


Assuming A column has Feature ID, B C & D has SWD ID & f has SWD ID & for F SW ID you want the Feature ID


Index(result u want to display,MATCH('value to be matched',Range to look up for ,FALSE),1)

=Index(A2:A50,MATCH(F2,B2:D50 ,FALSE),1) Its an array formula u need to shift+ctrl+enter


try this it shld work, if not post the worksheet