Vlookup with mutiple lookup values

[Wa_Wa]

Is there a way to use vlookup to look up mutiple values?

An examples

Worksheet 1
A----B
Big--Cat
Big--Dog

Worksheet 2
A----B---C
Big--Cat--1
Big--Dog--2

so would vlookup(A2&B2,Worksheet2!A1:C3,3) - would return 2
ie want the value for Big+Dog not the first return of Big in table 2

hopefully this doesn't sound too stupid - oh and I am not able to use a pivot table at the moment

 

[Peter_SSs]

Here is one way. I have done it on a single sheet, but the concept will work across two.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. Formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Small	Cat	4		Big	Cat	1
2	Big	Dog	2		Big	Dog	2
3	Small	Mouse	3		Small	Mouse	3
4	Big	Cat	1		Small	Cat	4
Lookup Multiple

 

[Wa_Wa]

Wow! Thanks very much