Using VLOOKUP to match 2 cells sheet2 to sheet1 and return the 3rd cell

Question

I'm hoping someone can help me, i have been using VLOOKUP to populate worksheets with Inventories, however I'm stuck with an issue where one column has the same value in multiple cells, I need to match 2 cells from sheet 2 with sheet 1 and have it return the 3rd cell from sheet 1 into sheet 2.

I'm working with about 350 rows in both sheets, and in some cells the same/different values repeat it self hence needing it to match with 2 cell in the same row.

this is the formula

=VLOOKUP(A1&L1,'Sheet1'!$A$1:$E$351,3,FALSE)

I'm expecting it to return the value that's in the 3rd column on sheet 1 in the row that matched the values of Cell A1 and L1 in sheet 2. and the same going on A2 & L2 then A3 & L3 and so forth.

I hope this is making sense haha :-)

Thanks!

Answer 1

The only way to do this task is to use a helper column

Go to Sheet one where the data table is, insert a column at the starting point of data e.g your Data set starting in SHEET1 from the column A. so insert new column in A use this formula in A1

=CONCATENATE(B1,C1,D1,E1,F1) Press Enter, Drag the formula down to A351

now go to sheet2 and use this formula in the result cell

=VLOOKUP(A1&L1,'Sheet1'!$A$1:$F$351,4,0)

Answer 2

Unless you have values in Column A of your 'Sheet1' that are the equal to to concatenation of your values in Columns A and L in 'Sheet2', the formula will not work.

Instead, I'd try FILTER if you have the newest version of Excel. Something like:

=FILTER('Sheet1'!$C$1:$C$351, 
(('Sheet1'!$A$1:$A$351=A1)*('Sheet1'!$L$1:$L$351=L1)))

Another option is INDEX. Something like:

=INDEX('Sheet1'!$C$1:$C$351, 
MATCH(A1&L1, 'Sheet1'!$A$1:$A$351&'Sheet1'!$L$1:$L$351,0),
3)