IF statement and lookup

**mwhiteley** · 03-17-2013, 08:56 AM

Hi,

Apologies but I don't really know what to search for in terms of what I want. What I am looking to do is create a function which does the following:

IF (the value in AG2 = a value which appears in column L in Sheet 1) AND (the value in A2 = a value which appears in column A in sheet 1) Then look up the value in column I

Hope someone can help with this.

Kind regards

**dilipandey** · 03-17-2013, 09:06 AM

Hi mwhiteley,

welcome to the forum.

Can you upload a sample file with your expected results.. thx

Regards,
DILIPandey
<click on below * if this helps>

**mwhiteley** · 03-17-2013, 09:42 AM

Hi,

Sorry I have had to make a rework as the oringinal is over 1MB. In the worksheet 'HOLDING' cell B2 should look at the value in B1 and try and find that value in Sheet 1 in column B and look at the value in A2 in 'HOLDING' and check to see if the value is in column A in Sheet 1. If both match then read the value in column C. An example is B2 should have the value 15, C2 should be 10.

**Tony Valko** · 03-17-2013, 10:03 AM

Try one of these...

This array formula** entered in B2:

=INDEX(Sheet1!$C$1:$C$4,MATCH($A2,IF(Sheet1!$B$1:$B$4=B$1,Sheet1!$A$1:$A$4),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to C2 then down as needed.

If the combination of date + Example will ALWAYS be unique then you can use this less complex normally entered formula.

=SUMPRODUCT(--(Sheet1!$A$1:$A$4=$A2),--(Sheet1!$B$1:$B$4=B$1),Sheet1!$C$1:$C$4)

**mwhiteley** · 03-18-2013, 09:43 AM

Thanks for your reply Tony it works. I have another query though I'm afraid, I have a worksheet which contains a cell which has the value 11/3/2013 in it with a Custom format (dd-mmm) which effectively displays as 11-Mar.

I have another cell which has the value 01/03/2013 08:01:01. What I need to do is set the formats or values to each cell to ensure that Cell1=Cell2 in the if statement?

Hope this makes sense thanks again

**Tony Valko** · 03-18-2013, 10:38 AM

Something like this...

A1 = 11-Mar
B1 = 01/03/2013 08:01:01

IF(A1=INT(B1),.........

**mwhiteley** · 03-18-2013, 11:53 AM

Thats great thanks very much Tony really appreciated!

**Tony Valko** · 03-18-2013, 02:10 PM

You're welcome!

IF statement and lookup

LinkBack

Thread Tools

Rate This Thread

Display

IF statement and lookup

Re: IF statement and lookup

Re: IF statement and lookup

Re: IF statement and lookup

Re: IF statement and lookup

Re: IF statement and lookup

Re: IF statement and lookup

Re: IF statement and lookup

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions