IF Statement Issues

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

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.

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

Formula:

=E4&"_"&F4&"_"&D4&"_"&C4

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

Formula:

=MATCH(P3&"_"&P4&"_"&P5&"_"&P6,Sheet2!$B$4:$B$35,FALSE)

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.

Formula:

=IF(ISERROR(MATCH(P3&"_"&P4&"_"&P5&"_"&P6,Sheet2!$B$4:$B$35,FALSE)),1,"")

Hi,

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

Formula:

E4&"_"&F4&"_"&D4&"_"&C4

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.

Originally Posted by 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.

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

Formula:

=INDEX(Sheet2!$G$4:$G$35,MATCH(P3&"_"&P4&"_"&P5&"_"&P6,Sheet2!$B$4:$B$35,FALSE),1)

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.

Formula:

=E4&"_"&F4&"_"&D4&"_"&C4

Originally Posted by 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)

Is Column P on Sheet 2 the helper column?

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

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

Formula:

=Q4&"_"&R4

and not

Formula:

=R4&"_"&Q4

Attach the workbook.

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

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.