Help adding 1 more item to this formula

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
803
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

Well-known Member
Joined
May 23, 2005
Messages
803
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 3, 2002
Messages
821
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...

Code:
=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

Well-known Member
Joined
May 23, 2005
Messages
803
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I am in Excel 2007

lol

In that case, you just need a single formula:

I5:

Control+shift+enter...

Code:
=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

Well-known Member
Joined
May 23, 2005
Messages
803
Office Version
  1. 365
Platform
  1. Windows
Aladin,

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

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top