Excel index match with two different sheets gives me #VALUE error

1
votes

I am trying to do an index match on two tables from two different sheets.

I have tried using a + sign to add the two different index match formulas I have tried to add the index and match functions with commas

=IFERROR(INDEX(Building!$H$4:$H$3490,MATCH($I5,Building!$A$4:$A$3490,0)),0)

works

=IFERROR(INDEX(AltCapID!$H$2:$H$608,MATCH($A5,AltCapID!$A$2:$A$608,0)),0)

works

what I want is if the first condition doesn't exist to execute the second formula

Current formula:

=IFERROR(INDEX(Building!$H$4:$H$3490,MATCH($I6,Building!$A$4:$A$3490,0)),0)+IFERROR(INDEX(AltCapID!$H$2:$H$608,MATCH($A6,AltCapID!$A$2:$A$608,0)),0)

I6 matches the value in Building!$A$4:$A$3490 so I am expecting the results from Building!$H$4:$H$3490 instead I am getting an #VALUE error

2
excelexcel-formula
What is the result of the two formulas that work? - cybernetic.nomad

2 Answers

0
votes

After reading my answer again, I realized all I needed was an if statement. So I added

=IF(A5="",IFERROR(INDEX(Building!$H$4:$H$3490,MATCH($A5,AltCapID!$A$2:$A$608,0)),0)),IFERROR(INDEX(AltCapID!$H$2:$H$608,MATCH($A5,AltCapID!$A$2:$A$608,0)),0))

In my table Column A is empty if that cell is to be used

0
votes

After editing your question, what you are looking is the following formula:

 =IFERROR(INDEX(Building!$H$4:$H$3490,MATCH($I5,Building!$A$4:$A$3490,0)),IFERROR(INDEX(AltCapID!$H$2:$H$608,MATCH($A5,AltCapID!$A$2:$A$608,0)),0))