# how to use index and match function

#### saurabhji

##### New Member
how to get the following result with the below sheet
 ! 1 a @ 1 b # 1 c \$ 1 d % 2 a ^ 2 b & 2 c ( 2 d ) 3 a * 3 b

<tbody>
</tbody>

for the above sheet i want the result
 1 a !(formulae should give this value) 1 c # 2 b ^ 3 b *

<tbody>
</tbody>
sheet is very big and lot of formulae are there so no copy past kindly help

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Smitty

##### Legend
Welcome to the Board!

You'll need a helper column to create unique values:

Excel 2012
ABCDE
1ResultData1Data2Helper
2!1a1a
3@1b1b
4#1c1c
5\$1d1d
6%2a2a
7^2b2b
8&2c2c
9(2d2d
10)3a3a
11*3b3b
12
13Data1Data2Result
141a!
151c#
162b^
173b*
Sheet1
Cell Formulas
RangeFormula
E2=C2&D2
E3=C3&D3
E4=C4&D4
E5=C5&D5
E6=C6&D6
E7=C7&D7
E8=C8&D8
E9=C9&D9
E10=C10&D10
E11=C11&D11
C14=INDEX(\$A\$2:\$A\$11,MATCH(A14&B14,\$E\$2:\$E\$11,0),0)
C15=INDEX(\$A\$2:\$A\$11,MATCH(A15&B15,\$E\$2:\$E\$11,0),0)
C16=INDEX(\$A\$2:\$A\$11,MATCH(A16&B16,\$E\$2:\$E\$11,0),0)
C17=INDEX(\$A\$2:\$A\$11,MATCH(A17&B17,\$E\$2:\$E\$11,0),0)

HTH,

#### gaz_chops

##### Well-known Member
You don't say where your data is! I assume it starts in column A

A1&B1 refers to your data in the bottom example

=index(\$A\$1:\$A\$100,match(A1&B1,\$C\$1:\$C\$100&\$D\$1:\$D\$100,0))

You must enter as an array (Ctrl, Shift & Enter)

Last edited:

#### saurabhji

##### New Member
thanks very much i will just check out in my sheet then come back
but thanks a lot

saurabh kumar

#### saurabhji

##### New Member
thanku very much i just now checked in my sheet and its really working very fine.
thanks a lot

saurabh kumar

You're welcome.

#### saurabhji

##### New Member
in excel is it possible to put logical reasoning with color
eg

 a b c 400 500 700 478 456 654 345 566 455 789 765 556

<tbody>
</tbody>

i want to put a formulae if any cell is greater then 500 paint it red or full column to be painted red

thanks

Last edited:

#### saurabhji

##### New Member
is it possible to use vlookup with OR FUNCTION???
OR HOW TO COLLECT DATA FROM 2 DIFFERENT SHEET WITH VLOOKUP FUNCTION
FOR EG: SHEET 1 SOME DATA
SHEET 2 SOME DATA

IN SHEET 3 I WANT TO VLOOKUP SOME CELL EITHER FROM SHEET 1 OR SHEET 2
I CANNOT MERGE SHEET 1 AND 2

#### saurabhji

##### New Member
When we use vlookup and extract some data from sheet 1 in that how to copy cell color also

Replies
14
Views
648
Replies
6
Views
288
Replies
1
Views
359
Replies
8
Views
390
Replies
5
Views
1K

1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

### 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?

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