INDEX and MATCH

jislandhopper

Board Regular
Joined
Jul 23, 2013
Messages
92
Afternoon Everyone,

I hope someone can assist with a small problem. I want to get a formula which searches 2 criteria and return a answer. It's like a Vlookup but with an additional criteria. I've tried consolidating the cells but someone told me that using INDEX and MATCH are a better was to do this. I've tried writing the formula but it doesn't seem to work, so I'm hoping the community can help me out.


I've created a simplified reference table and a small table as example as what I'm after in return. The reference table is where I pull the information from and I want the formula to review Column A and B from the reference data and return an answer on. Column D on the results table. I've added in the reference numbers which I added manually. Can anyone point me in the right direction?



Reference table
Col ACol BCol C
Depo 106/08/2019REF 123
Depo 107/08/2019REF 124
Depo 108/08/2019REF 125
Depo 109/08/2019REF 126
Depo 110/08/2019REF 127
Depo 111/08/2019REF 128
Depo 112/08/2019REF 129
Depo 113/08/2019REF 130
Depo 114/08/2019REF 131
Depo 215/08/2019REF 132
Depo 216/08/2019REF 133
Depo 217/08/2019REF 134
Depo 218/08/2019REF 135
Depo 219/08/2019REF 136
Results Table
Col ACol BCol D
DEPO Date from Ref number
Depo 113/08/2019 REF 130
Depo 108/08/2019 REF 125
Depo 222/08/2019 REF 139
Depo 219/08/2019 REF 136

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Thanks,
Jason
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
How about

Book1
ABCDEFGHI
1Col ACol BCol CDEPODate fromRef number
2Depo 106/08/2019REF 123Depo 113/08/2019REF 130
3Depo 107/08/2019REF 124Depo 108/08/2019REF 125
4Depo 108/08/2019REF 125Depo 222/08/2019#N/A
5Depo 109/08/2019REF 126Depo 219/08/2019REF 136
6Depo 110/08/2019REF 127
7Depo 111/08/2019REF 128
8Depo 112/08/2019REF 129
9Depo 113/08/2019REF 130
10Depo 114/08/2019REF 131
11Depo 215/08/2019REF 132
12Depo 216/08/2019REF 133
13Depo 217/08/2019REF 134
14Depo 218/08/2019REF 135
15Depo 219/08/2019REF 136
Sheet2S
Cell Formulas
RangeFormula
I2=INDEX($C$2:$C$15,MATCH(G2&"|"&H2,INDEX($A$2:$A$15&"|"&$B$2:$B$15,0),0))
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top