a complicated vlookup?

bmwbuyer2007

Board Regular
Joined
Jul 19, 2007
Messages
82
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 :)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try like this...

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

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

Well-known Member
Joined
Dec 8, 2006
Messages
2,222
Office Version
  1. 2010
Following C Perason's site a formula like
Code:
=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"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,780
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top