How to VLookup on multiple criteria?

[gorgon777]

Hi,

I have the below two tables in two separate worksheets:

POSummary
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]PO[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]01.01.2011[/TD]
[TD]10987[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01.02.2011[/TD]
[TD]10987[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01.03.2011[/TD]
[TD]10987[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








KRANData
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]PO[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]01.12.2010[/TD]
[TD]10987[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]01.01.2011[/TD]
[TD]10987[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]01.02.2011[/TD]
[TD]10987[/TD]
[TD]SA[/TD]
[/TR]
[TR]
[TD]01.03.2011[/TD]
[TD]10987[/TD]
[TD]AUS[/TD]
[/TR]
</tbody>[/TABLE]










What I would like to do is lookup the 'Name' from the KRANData table by using the 'Date' and 'PO' values within the POSummary table. For example, the 'Name' value in the first row of the POSummary table should be USA and the second row should be SA.

How can i achieve this?

With Thanks

 

[sanrv1f]

try this

Excel Workbook
	A	B	C
1	Date	PO	Name
2	01.12.2010	10987	UK
3	01.01.2011	10987	USA
4	01.02.2011	10987	SA
5	01.03.2011	10987	AUS
Sheet2

Excel 2010

Excel Workbook
	A	B	C
1	Date	PO	Name
2	01.01.2011	10987	USA
3	01.02.2011	10987	SA
4	01.03.2011	10987	AUS
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[raj2206]

Hi, Thanks for writing!!

I have your table POSummary at A1:C4 and the table KRANData at F1:H5

In C2 (the Name Column of the POSummary table you can use this formula:

=INDEX($H$2:$H$5,SUMPRODUCT(($F$2:$F$5=A2)*($G$2:$G$5=B2)*(ROW($F$2:$F$5)-ROW($F$2)+1)))

Drag the formula down.

Hope this helps!

Thanks/Raj

 

[gorgon777]

Hi, Thanks for writing!!

I have your table POSummary at A1:C4 and the table KRANData at F1:H5

In C2 (the Name Column of the POSummary table you can use this formula:

=INDEX($H$2:$H$5,SUMPRODUCT(($F$2:$F$5=A2)*($G$2:$G$5=B2)*(ROW($F$2:$F$5)-ROW($F$2)+1)))

Drag the formula down.

Hope this helps!

Thanks/Raj

Thank you both Raj and Sanv, both solutions have worked great!!

 

[Fathermoyo]

Since column B in both tables have similar contents you would also use a simple vlookup formula. Click onthe first cell of the first row in the POSummary table then insert vlookup formula from formula bar. Function argument box will appear.In the lookup value click 01.01.2011 of the first row, second column of the POSummary table. In the table array field select values in the KRAData table. Fix both values as follows, =VLOOKUP(A2,Sheet2!$A$3:$C$6,3) then double click on the right of the box in the first row of the column where you inserted formula. You will get those result as you wanted

I hope this can be another alternative

 

[drgs]

Hi, Thanks for writing!!

I have your table POSummary at A1:C4 and the table KRANData at F1:H5

In C2 (the Name Column of the POSummary table you can use this formula:

=INDEX($H$2:$H$5,SUMPRODUCT(($F$2:$F$5=A2)*($G$2:$G$5=B2)*(ROW($F$2:$F$5)-ROW($F$2)+1)))

Drag the formula down.

Hope this helps!

Thanks/Raj

A dangerous formula, if there is more than one match