A rather involved IF function!

braderz

New Member
Joined
Sep 26, 2013
Messages
16
Hi Again,

Struggling to come up with a solution to the following.

Im looking to create an IF formula for the following situation.

If another worksheet has the same value as the current worksheet then display the corresponing cell value of the other worksheet that lies in that row. The range of data to look up is say the whole of column C and then the output consists of the matching results in column A.

So there is a lot of data in each rows and need to output all of the matching entries.

Is this possible, any help would be most appreciated.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Daveydoodles

Board Regular
Joined
May 21, 2013
Messages
215
It sounds like you are looking for a lookup function.

If the values in the first sheet that you want to find are in column A, and you want to find them in column C of the second sheet and return the corresponding value from column A of that second sheet, use;

INDEX(Sheet2!A:A,MATCH(Sheet1!A1,Sheet2!C:C,0))
 

braderz

New Member
Joined
Sep 26, 2013
Messages
16
It sounds like you are looking for a lookup function.

If the values in the first sheet that you want to find are in column A, and you want to find them in column C of the second sheet and return the corresponding value from column A of that second sheet, use;

INDEX(Sheet2!A:A,MATCH(Sheet1!A1,Sheet2!C:C,0))

Hi again,

I have tried the following Index but doesn't seem to be given an output, I have attached the formula I am using and the source:

{=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))}

It is exactly the same as in the source but doesn't seem to be working?

Source: http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/</SPAN>


I tried INDEX(Sheet2!A:A,MATCH(Sheet1!A1,Sheet2!C:C,0)) also and that doesn't work plus don't think it would be suitable as I need to consider multiple matches.

Any help would be most appreciated
 

Jubjab

Well-known Member
Joined
Jan 3, 2007
Messages
993
Did you enter the array formula correctly? I.e. without the curly brackets and confirm formula with CTRL-SHIFT-ENTER ?
 

braderz

New Member
Joined
Sep 26, 2013
Messages
16
Did you enter the array formula correctly? I.e. without the curly brackets and confirm formula with CTRL-SHIFT-ENTER ?

Fantastic many thanks for that, will I always have to use that to run it?
 

Jubjab

Well-known Member
Joined
Jan 3, 2007
Messages
993
Array formulas are always entered like that, but once it is entered, you don't need to do anything for it to "run".
 

Forum statistics

Threads
1,147,823
Messages
5,743,405
Members
423,792
Latest member
travisds

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