Formula Needed!!

Degaulle

Board Regular
Joined
Nov 1, 2004
Messages
62
Hi

I need to be able to look up a value of a product code that there are two of the same. There is a variable, the catalogue code.

In the example i need to look up the qty of code 401 for the SR Catalogue.
Book1
ABCDE
1CodeNameCatalogueTypePriceQTYOnOrder
2401FFS160100
3401FFSR16072
4402SSS19415
5402SSSR19445
Sheet1
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Create in another column, a unique value by concatenating, then use Index/Match
Book2
ABCDE
1CodeCatalogueQty401SR
2401S100401S75
3401SR75401SR
4402S15402S
5402SR45402SR
Sheet1


In column C

=A2&CHAR(127)&B2 and drag down

In E2,

=INDEX($C$2:$C$5,MATCH(D1&CHAR(127)&E1,$D$2:$D$5,0))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Book1
ABCDEF
1CodeNameCatalogue TypePriceQTY On OrderConcat
2401FFS160100401,FF,S
3401FFSR16072401,FF,SR
4402SSS19415402,SS,S
5402SSSR19445402,SS,SR
6
7
8CodeCat TypeQty
9401SR72
10402S15
11
Sheet1


F2, copied down:

=A2&","&B2&","&C2

C9, copied down:

=INDEX($E$2:$E$5,MATCH(A9&",*"&B9,$F$2:$F$5,0))
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How about

=SUMPRODUCT(--(A2:A5=401),--(C2:C5="SR"),E2:E5)


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

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