how to use index and match function

saurabhji

New Member
Joined
Jun 4, 2015
Messages
11
how to get the following result with the below sheet
!1a
@1b
#1c
$1d
%2a
^2b
&2c
(2d
)3a
*3b

<tbody>
</tbody>
















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

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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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,
 
Upvote 0
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:
Upvote 0
thanku very much i just now checked in my sheet and its really working very fine.
thanks a lot

saurabh kumar
 
Upvote 0
sir one more information
in excel is it possible to put logical reasoning with color
eg


abc
400500700
478456654
345566455
789765556

<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
 
Upvote 0
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

 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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