a complicated vlookup?

[bmwbuyer2007]

hi all,

I have 2 sheets in my workbook, with very simple data:

Sheet 1

Count Product
3 A
11 A1
3 A2
1 A3
1 A4
1 A6

Sheet 2

Count Product
2 A
1 AL
1 AM
2 A3
3 A2
11 A5

I would like a formula in sheet 2, column C, which will look for the product code in sheet 1 and return the figure to the left of it, so sheet 2 will then look like:

Sheet 2

Count Product Count 2
2 A 3
1 AL
1 AM
2 A3 1
3 A2
11 A5 3

Any ideas please?

Thanks

 

[Jonmo1]

Try like this...

=INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0))

 

[MisterCrash]

How about:

=IF(COUNTIF(Sheet1!$B$2:$B$7, B2)=0, "", INDEX(Sheet1!$A$2:$A$7, MATCH(B2, Sheet1!$B$2:$B$7, 0)))

 

[njimack]

Your formula in Sheet2 C2 would be:
=IF(COUNTIF(Sheet1!$B$2:$B$7,B2),INDEX(Sheet1!$A$2:$A$7,MATCH(B2,Sheet1!$B$2:$B$7,0)),"")

(copy down as required)

 

[arthurbr]

Following C Perason's site a formula like

=OFFSET(LLTable,MATCH(F67,OFFSET(LLTable,0,1,ROWS(LLTable),1),0)-1,0,1,1)

would do the job

replace LLTable with sheet2!a2:b7 and f67 is the value to look up

You can find info on http://www.cpearson.com/excel/TablesAndLookups.aspx under "Left Lookup"

 

[bmwbuyer2007]

Thanks to everyone who responded, this is working now