IF Statement Issues

[ruddellq]

I am using an If statement to do a look up in a table of values (all numbers). I have it created so that it will go through a set of IF statements and return a 1 if all of those statements are false. It keeps returning the answer false instead of the 1 I am wanting. See below for statement.

=IF(P3=2,IF(P4=50,IF(P5=12,IF(P6=43,Sheet2!$G$8,IF(P3=2,IF(P4=50,IF(P5=12,IF(P6=54,Sheet2!$G$9,IF(P3=2,IF(P4=50,IF(P5=12,IF(P6=68,Sheet2!$G$10,IF(P3=2,IF(P4=50,IF(P5=12,IF(P6=97,Sheet2!$G$11,1))))))))))))))))

Thank you for your help.

[PFDave]

You don't have a false statement for the first 4 IFs and loads in between.

So IF P3 doesn't = 2 then it will return FALSE. Then lots of those inbetween.

A sample file would be best here so we can re-write your formula to correct effect.

[Richard Buttrey]

Almost certainly you need a VLOOKUP table, so as Dave suggests upload the workbook.

[ruddellq]

The IF statements are labeled as false at the moment. The table lookup is on the second page. I would use a Vlookup but I am changing multiple parameters not just a simple column row table. Right now the if statement is working exactly how I want it to with the pulling from the multiple parameters the only thing is I want it to a return a 1 if all of those statements are false.

[Richard Buttrey]

Hi,

I still can't help thinking that this could be simplified considerably by adding an additional column A to your data on sheet2. So A4 copied down

Please Login or Register  to view this content.

I'm assuming you're wanting to know whether the values in P3:P6 on sheet 1 are present in the Sheet2 table, hence a simple

Please Login or Register  to view this content.

will return the row number if those P3:P6 values are present. And if you want to return a value of 1 if they're not then wrap it in an IF(ISERROR()) function

i.e.

Please Login or Register  to view this content.

[ruddellq]

When you say add an additional column A to my data table what would go in this column?

Are your second and third statement the way to use multiple parameters (ie P3:P6) to look up data in my table?

[ruddellq]

Essentially what I want to do is create a statement that will use P3:P6 to match the first four column of my data table and return the value of the 5th column.

So if P3:P6 equals 1, 33, 6, 43 then I would get 5347. (The first row of data table.) If there is an easier way to do this than an if statement I am all for it.

[Richard Buttrey]

Hi,

I meant populate the column A alongside your table. i.e in A4 enter

Please Login or Register  to view this content.

and copy down.

I am presuming that you want in some way to check whether all the P3:P6 values etc. on sheet 1 are in your table, and concatenating the relevant table values in one cell is a common way to do this. If the MATCH formula finds that concatenated string then it will return the number of the row where it's found. Obviously if it's not found then the MATCH returns an Error and hence the =IF(ISERROR()) returns the 1 you said you needed

If however I've misunderstood and this is not what you're after then explain in a narrative comment what you're trying to do.

[Richard Buttrey]

Essentially what I want to do is create a statement that will use P3:P6 to match the first four column of my data table and return the value of the 5th column.

So if P3:P6 equals 1, 33, 6, 43 then I would get 5347. (The first row of data table.) If there is an easier way to do this than an if statement I am all for it.

OK, I hadn't seen this post when I posted just now.

So as I said add the helper column A formula I mentioned and to find the 5th column value use the formula

Please Login or Register  to view this content.

[ruddellq]

When I enter that formula I get #N/A. Check to see if I entered the helper column correctly.

[Richard Buttrey]

Hi,

In sheet2 you just forgot to preface the A4 formula with the = sign to make it a formula and not just any old string. i.e.

Please Login or Register  to view this content.

[ruddellq]

That did the trick! Thank you so much for all your help!

[ruddellq]

I'm running into another problem. I 'm trying to do the same thing with a different data table. I created the helper column again and copied over the formula but it keeps spitting out #n/a.

=INDEX(Sheet2!$S$4:$S$10,MATCH(V2&"_"&V3,Sheet2!$P$4:$P$10,FALSE),1)

[AliGW]

Attach the workbook.

[ruddellq]

I'm running into another problem. I 'm trying to do the same thing with a different data table. I created the helper column again and copied over the formula but it keeps spitting out #n/a.

=INDEX(Sheet2!$S$4:$S$10,MATCH(V2&"_"&V3,Sheet2!$P$4:$P$10,FALSE),1)

[Richard Buttrey]

I'm running into another problem. I 'm trying to do the same thing with a different data table. I created the helper column again and copied over the formula but it keeps spitting out #n/a.

=INDEX(Sheet2!$S$4:$S$10,MATCH(V2&"_"&V3,Sheet2!$P$4:$P$10,FALSE),1)

Is Column P on Sheet 2 the helper column?

Have you manually checked that the V2 & "_" & V3 string does exist in column P?

[ruddellq]

The problem is occurring with the Weld Connections. (Weld Strength) The lookup table is on the 2nd sheet.

[ruddellq]

Column P is the helper column correct. V2 and V3 do exist in column P.

[AliGW]

33_97 doesn't exist on the second sheet, but 97_33 does - you have got the numbers the wrong way round.

[Richard Buttrey]

The helper column P needs to have the Yield & Gauge idenifiers in the same order as V2 & V3. i.e. you have them the wrong way round.
P4 should be

Please Login or Register  to view this content.

and not

Please Login or Register  to view this content.

[JohnTopley]

You lookup "33_97" but the table has "97_33" : hence no match.

[ruddellq]

That did the trick. I've been in excel to long. Thank you for your help!

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.