Excel "Left VLOOKUP" using INDEX MATCH - have I got it? 1

[PWD]

Good afternoon I think I've got it but just wanted confirmation. I have 2 worksheets that have matching data in columns "M" & "L" and I need to populate the second sheet with the values from column "A" of the first sheet. This is my formula:

=INDEX(Sheet1!A:A,MATCH(Sheet2!M1, Sheet1!L:L,0),1)

Yes?

Many thanks,
D€$

 

[SkipVought]

so you're saying that sheet1 has a table that includes columns A:L, and the value in sheet2!M1, can be found in sheet1 column L where the value in column A on that row, you want returned?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

If there is no matching data, how do you want to handle the error?

One possibility (assuming you use Excel 2007 or later) is:

=IFERROR(INDEX(Sheet1!A:A,MATCH(M1,Sheet1!L:L,0),1),"no match")

I deleted the reference to Sheet2 for cell M1 because your formula is presumably going on Sheet2.

If you use Excel 2003 or earlier, it would be:

=IF(ISNA(MATCH(M1,Sheet1!L:L,0)),"no match",INDEX(Sheet1!A:A,MATCH(M1,Sheet1!L:L,0),1))

Brad

 

[PWD]

Hi Skip, very succinct! Basically I processed a sample twice and each time it generated a different serial number (Column"A") although the reference numbers (Column "L" in Sheet1 and Column "M" [+1 for the column I inserted for this formula]) are the same. So I needed to identify the initial serial numbers alongside the reprocessed serial numbers.

Hi Brad, yes nice & tidy! I was only thinking about 2007 onwards.

Many thanks,
D€$