Help adding 1 more item to this formula

[thewiseguy]

Hi everyone,

Hopefully I can explain this correctly. Here is my current formula:

=IF(ISERROR(INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0))),"0",INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0)))

I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.

Any help is greatly appreciated.


-m

 

[Domenic]

Is this what you mean?

=IF(ISNUMBER(MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,IF(Sheet3!$E$5:$E$200=H5,Sheet3!$A$5:$A$200)),0)),INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,IF(Sheet3!$E$5:$E$200=H5,Sheet3!$A$5:$A$200)),0)),"0")

...confirmed with CONTROL+SHIFT+ENTER. Note that the value if false, in this case 0, will be returned as a text value. If you wish to have it returned as a numerical value, remove the quotes around the 0.

Hope this helps!

 

[thewiseguy]

Maybe if I start from scratch it would be more clear.

Sheet 3 is where my table is located.

The table goes from Rows 5: 200, and columns A:F.

Sheet 1, cell I5 is where I would like the formula to return the value (i will be dragging this formula down).

I need I5 essentially to say " where Sheet3 column A = sheet1 F5, where sheet3 column B = G5, where sheet3 column C = C5 and where sheet3 column E = D5, then return value Sheet3 column D.

I am matching 4 values to return 1.

Hopefully this is more clear.

-m

 

[klb]

A "simple" if/and should do the trick. You can subsitute the actual cells:

if(and(Sheet3 column A = sheet1 F5, sheet3 column B = G5, sheet3 column C = C5, sheet3 column E = D5),Sheet3 column D,0)

 

[Domenic]

What type of data does Column D on Sheet 3 contain, numerical or text?

 

[Aladin Akyurek]

Maybe if I start from scratch it would be more clear.

Sheet 3 is where my table is located.

The table goes from Rows 5: 200, and columns A:F.

Sheet 1, cell I5 is where I would like the formula to return the value (i will be dragging this formula down).

I need I5 essentially to say " where Sheet3 column A = sheet1 F5, where sheet3 column B = G5, where sheet3 column C = C5 and where sheet3 column E = D5, then return value Sheet3 column D.

I am matching 4 values to return 1.

Hopefully this is more clear.

-m

Use a two-cell approach unless you are on Excel 2007...

On Sheet1...

I5:

=IF(ISNUMBER(G5),INDEX(Sheet3!$D$5:$D$500,J5),0)

J5:

Control+shift+enter...

=MATCH(
    IF(Sheet3!$A$5:$A$500=F5,
    IF(Sheet3!$B$5:$B$500=G5,
    IF(Sheet3!$C$5:$C$500=C5,
    IF(Sheet3!$E$5:$E$500=D5, 1)))),0)

 

[thewiseguy]

What type of data does Column D on Sheet 3 contain, numerical or text?

text in numerical form, i.e "DY 232 IS WV-L" (is one example)

 

[thewiseguy]

I am in Excel 2007

lol

 

[Aladin Akyurek]

I am in Excel 2007

lol

In that case, you just need a single formula:

I5:

Control+shift+enter...

=IFERROR(
    INDEX(Sheet3!$D$5:$D$500,
      MATCH( 
         IF(Sheet3!$A$5:$A$500=F5, 
         IF(Sheet3!$B$5:$B$500=G5, 
         IF(Sheet3!$C$5:$C$500=C5, 
         IF(Sheet3!$E$5:$E$500=D5, 1))))),0)

 

[thewiseguy]

Aladin,

I put the formula in exactly as you typed, but it is telling me there are too few arguments. Any ideas?