please help..INDEX and match query

[venkat2311]

I am trying to use INDEX and MATCH to help me to lookup another file to update values.
File to be updated -
Luni_report_try1.xlsx

Values in column A & B (Volume and storage system names) to be searched on the second file (the lookup file – luni_trials_ext1.xlsx) and return Parity Group value (Col E) to be returned from second file where there is a match. The value should be updated on Original PG column.
I also need to return the value of Old Tier from CMO Old tier col.
Please can you help and I am struggling with INDEX match to lookup values from multiple columns.

Luni_report_try1.xlsx

Volume Storage System Host Group/iSCSI Target Capacity Parity Group Tier Original PG Old Tier
00:01:3E OSA2-USPV-DC2 LUNID7, LUNID8 34.00 GB 1-6-1 E1-1 2
00:01:3E OSA3-USPV-DC3 LUNID7, LUNID8 34.00 GB 1-6-1
00:01:3F OSA2-USPV-DC2 LUNID7, LUNID8 34.00 GB 1-6-1
00:01:3F OSA3-USPV-DC3 LUNID7, LUNID8 34.00 GB 1-6-1
00:06:53 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:53 OSA3-USPV-DC3 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:55 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:55 OSA3-USPV-DC3 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:57 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:57 OSA3-USPV-DC3 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:59 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB 1-7-1
00:06:59 OSA3-USPV-DC3 LUNID7, LUNID8 8.50 GB 1-7-1


luni_trials_ext1.xlsx

Volume Storage System Host Group/iSCSI Target Capacity Original Parity Group CMO Old Tier
00:01:3E OSA2-USPV-DC2 LUNID7, LUNID8 34.00 GB E1-1 2
00:01:3F OSA2-USPV-DC2 LUNID7, LUNID8 34.00 GB E1-1 2
00:06:53 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:55 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:57 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:59 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:5B OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:5D OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:5E OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:5F OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:60 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:61 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:62 OSA2-USPV-DC2 LUNID7, LUNID8 8.50 GB E1-1 2
00:06:65 OSA2-USPV-DC2 LUNID7, LUNID8 17.01 GB E6-2 2
00:06:66 OSA2-USPV-DC2 LUNID7, LUNID8 17.01 GB E6-2 2
00:06:67 OSA2-USPV-DC2 LUNID7, LUNID8 17.01 GB E6-2 2
00:06:68 OSA2-USPV-DC2 LUNID7, LUNID8 17.01 GB E6-2 2
00:06:69 OSA2-USPV-DC2 LUNID7, LUNID8 17.01 GB E6-2 2
00:06:6A OSA2-USPV-DC2 LUNID7, LUNID8 17.01 GB E6-2 2
00:06:70 OSA2-USPV-DC2 LUNID7, LUNID8 34.03 GB E1-6 2

 

[Rijnsent]

Hi Venkat,

have you checked/tried these sites?
Two-column Lookup in Excel - Easy Excel Tutorial
And/or:
How to VLOOKUP with Multiple Criteria Using INDEX and MATCH in Excel
Another approach can be: add a column to the beginning of both files, "merging" the two columns, e.g. =A2&"-"&B2, so it becomes a simpler one-column VLOOKUP.
Cheers,
Koen