Help With IF INDEX AND MATCH??
 

I have a Sheet of IMPORTED DATA(Sheet1). On another sheet, I need a formula
compare the data in Col's B AND C then return the related data from coumn A.
I hope the example below describes it.

Thanks in advance

Sheet1
A B C
1 PN1
2 20x12
3 CB100
4 PN2
5 20x12
6 CC100
7 PN3
8 15x18
9 CC100
10 PN4
11 20x30
12 CB100


Sheet2
A B C
1 20x30 CB100 PN4
2 20x12 CC100 PN2
3 20x12 CB100 PN1
4 15x18 CC100 PN3

Help With IF INDEX AND MATCH??
 

Just try this in a duplicate copy of your sheet.

Since the data is not in a properly allocated to retrieve the result by
using a formula, just do like this€¦

I assume that your data Start from A1 cell and end with C12 cell.

Now place the cursor in A12 (since the end value is upto C12) and give
Cntrl+Shift+Up Arrow now it will be selected upto A1 cell. Press Cntrl+G and
now press Alt+S and finally press Alt+K. Now you can able to see the blank
cells of A1 to A12 will be get selected. Now press the Equal button (=) now
the equal will be typed in A2 cell just select above cell from the active
cell (that is A1) and give enter. Now press the Cntrl+D. All the blank
cells of A1 to A12 will be filled with the values according to the data.

Now again come to B Column that is B12 cell and give Cntrl+Shift+Up Arrow
and select upto B2 cell. Press Cntrl+G and now press Alt+S and finally press
Alt+K. Now you can able to see the blank cells of A1 to A12 will be getting
selected. Now press the Equal button (=) now the equal will be typed in A2
cell just select above cell from the active cell (that is A1) and give enter.
Now press the Cntrl+D. All the blank cells of B2 to B12 will be filled with
the values according to the data.

Now select the A & B column and do copy (Cntrl+C) and do a right click and
select Paste Special and select the option €˜Values and give Ok.

Now finally place the cursor in any cell of C Column and press Cntrl+G and
now press Alt+S and finally press Alt+K. Now you can able to see the blank
cells of C1 to C12 will be getting selected. Do a Right Click and select
Delete now a small popup window will appear and ask you the deleting method.
Select the Entire Row option and give Ok.

Now your data will be in a correct allocation. Now Select the whole data
from A1 to C4 and apply Sort option by giving Alt+D+S you can see a popup
window in that choose the Column B in sort by Drop Down List and select the
sort method as Descending and give Ok.

You can see your desired data now€¦

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Lemmesee" wrote:

I have a Sheet of IMPORTED DATA(Sheet1). On another sheet, I need a formula
compare the data in Col's B AND C then return the related data from coumn A.
I hope the example below describes it.

Thanks in advance

Sheet1
A B C
1 PN1
2 20x12
3 CB100
4 PN2
5 20x12
6 CC100
7 PN3
8 15x18
9 CC100
10 PN4
11 20x30
12 CB100


Sheet2
A B C
1 20x30 CB100 PN4
2 20x12 CC100 PN2
3 20x12 CB100 PN1
4 15x18 CC100 PN3

Help With IF INDEX AND MATCH??
 

Maybe I should have mentioned that the Impoted Data is over 4K rows long and
I cannot rearrange it due to Lots of other formulas that use the data.

"Ms-Exl-Learner" wrote:

Just try this in a duplicate copy of your sheet.

Since the data is not in a properly allocated to retrieve the result by
using a formula, just do like this€¦

I assume that your data Start from A1 cell and end with C12 cell.

Now place the cursor in A12 (since the end value is upto C12) and give
Cntrl+Shift+Up Arrow now it will be selected upto A1 cell. Press Cntrl+G and
now press Alt+S and finally press Alt+K. Now you can able to see the blank
cells of A1 to A12 will be get selected. Now press the Equal button (=) now
the equal will be typed in A2 cell just select above cell from the active
cell (that is A1) and give enter. Now press the Cntrl+D. All the blank
cells of A1 to A12 will be filled with the values according to the data.

Now again come to B Column that is B12 cell and give Cntrl+Shift+Up Arrow
and select upto B2 cell. Press Cntrl+G and now press Alt+S and finally press
Alt+K. Now you can able to see the blank cells of A1 to A12 will be getting
selected. Now press the Equal button (=) now the equal will be typed in A2
cell just select above cell from the active cell (that is A1) and give enter.
Now press the Cntrl+D. All the blank cells of B2 to B12 will be filled with
the values according to the data.

Now select the A & B column and do copy (Cntrl+C) and do a right click and
select Paste Special and select the option €˜Values and give Ok.

Now finally place the cursor in any cell of C Column and press Cntrl+G and
now press Alt+S and finally press Alt+K. Now you can able to see the blank
cells of C1 to C12 will be getting selected. Do a Right Click and select
Delete now a small popup window will appear and ask you the deleting method.
Select the Entire Row option and give Ok.

Now your data will be in a correct allocation. Now Select the whole data
from A1 to C4 and apply Sort option by giving Alt+D+S you can see a popup
window in that choose the Column B in sort by Drop Down List and select the
sort method as Descending and give Ok.

You can see your desired data now€¦

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Lemmesee" wrote:

I have a Sheet of IMPORTED DATA(Sheet1). On another sheet, I need a formula
compare the data in Col's B AND C then return the related data from coumn A.
I hope the example below describes it.

Thanks in advance

Sheet1
A B C
1 PN1
2 20x12
3 CB100
4 PN2
5 20x12
6 CC100
7 PN3
8 15x18
9 CC100
10 PN4
11 20x30
12 CB100


Sheet2
A B C
1 20x30 CB100 PN4
2 20x12 CC100 PN2
3 20x12 CB100 PN1
4 15x18 CC100 PN3

Help With IF INDEX AND MATCH??
 

Sheet2...

C2:

Control+shift+enter, not just enter:

=INDEX(Sheet1!$A$1:$A$10,MIN(IF(IF(Sheet1!$C$3:$C$ 12=B1,Sheet1!$B$2:$B$11)=A1,ROW(Sheet1!$A$1:$A$10) )))


Lemmesee wrote:
I have a Sheet of IMPORTED DATA(Sheet1). On another sheet, I need a formula
compare the data in Col's B AND C then return the related data from coumn A.
I hope the example below describes it.

Thanks in advance

Sheet1
A B C
1 PN1
2 20x12
3 CB100
4 PN2
5 20x12
6 CC100
7 PN3
8 15x18
9 CC100
10 PN4
11 20x30
12 CB100


Sheet2
A B C
1 20x30 CB100 PN4
2 20x12 CC100 PN2
3 20x12 CB100 PN1
4 15x18 CC100 PN3