Excel 2007 : IF Statement involving set of Data

**SuperHmong** · 04-25-2012, 09:52 AM

Hi all,

I had a formula in mind but I think it's a little impropable on Excel. It may require some VBS, which I'm not too familiar with. Any help is greatly appreciated.

Here is my dilema:

Logical Statement: If cell F2 equals anything in a set of cells (Column D).

If True: Return the data information from Column A of the same row. (I.E. If cell F2 equals a data set found in cell D5, then return the value in A5).

If False: "Not Found"

**dilipandey** · 04-25-2012, 09:56 AM

Hi SuperHmong,

Welcome to the forum.

You can use below formula to achieve the desired result:-

=OFFSET($A$1,MATCH($F$2,$D$2:$D$5,0),0)

Regards,
DILIPandey

<click on below 'star' if this helps>

**SuperHmong** · 04-25-2012, 10:19 AM

Can you explain the offset formula? I've never had to use it before.

Also, I forgot to mention that cell F2 in Sheet 1 needs to match any value in Sheet 2. The data table is in Sheet 2 and Sheet 1 is being used as a user-interface tool to pull data information.

**Cutter** · 04-25-2012, 10:28 AM

Isn't this more appropriate since OFFSET() is volatile - INDEX() is not?

=IFERROR(INDEX(Sheet2!A:A,MATCH(F2,Sheet2!D:D,0)),"Not Found")

**dilipandey** · 04-25-2012, 10:32 AM

Hi SuperHmong,

Offset formula moves the reference from the source (initial referenced cell) to a target cell based on the number of rows / columns you provide.

Syntax:-
OFFSET(reference,rows,cols,height,width)

so to explain better, =offset(a5,2,3) will provide the result from d7 cell.

Regarding inclusion of sheet references in the formula you can revise the formula as:-

=OFFSET(sheet2!$a$2,MATCH(sheet1!$F$2,sheet2!$D$2:$D$5,0),0)

Note:- I have manually edited the formula without using excel, so you may need to correct the references at your end seeing this one.

Regards,
DILIPandey

<click on below 'star' if this helps>

**SuperHmong** · 04-25-2012, 11:05 AM

The reference cell for Offset needs to be determined on it's own. I'm going to continue working on this, but thanks to everyone for putting me on the right path.