Index Match formula? Or something else?

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,033
Office Version
  1. 365
Platform
  1. Windows
I need a formula that returns a value from columns C-F based on 2 criteria in columns A & B.

So an example using the below;

Bravo, Item 3 - should return 6,8,5,6 when I drag the formula across

I think its index match but unsure :-/

TIA

ABCDEF
1AlphaItem 11349
2Item 22114
3Item 38288
4Item 43123
5
6BravoItem 14114
7Item 28288
8Item 36856
9Item 43123
10
11CharlieItem 11441
12Item 28818
13Item 35685
14Item 42322
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
practice.xlsx
ABCDEFGHIJKL
21AlphaItem 1Alpha Item 113491349
32AlphaItem 2Alpha Item 221142114
43AlphaItem 3Alpha Item 382888288
54AlphaItem 4Alpha Item 431233123
65    
76BravoItem 1Bravo Item 141144114
87BravoItem 2Bravo Item 282888288
98BravoItem 3Bravo Item 368566856
109BravoItem 4Bravo Item 431233123
1110    
1211CharlieItem 1Charlie Item 114411441
1312CharlieItem 2Charlie Item 288188818
1413CharlieItem 3Charlie Item 356855685
1514CharlieItem 4Charlie Item 423222322
Sheet6
Cell Formulas
RangeFormula
I2:L15I2=IFERROR(INDEX(E$2:E$15,MATCH(CONCATENATE($B2," ",$C2),$D$2:$D$15,0))," ")
D12:D15,D7:D10,D2:D5D2=B2&" "&C2


I only put additional column i.e Column D
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,033
Office Version
  1. 365
Platform
  1. Windows
Thanks Rajesh

Ideally I dont want to add a helper column, or have to copy down the names in column B

Makes it a bit trickier :(
 

Watch MrExcel Video

Forum statistics

Threads
1,126,972
Messages
5,621,913
Members
415,865
Latest member
pxmike

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