If Error Match Formula problem

[joshie78]

Hi, below is my formula I have that is working for a spreadsheet. It is pulling data based on a match from a different worksheet and sending back the correct data.

Example, if I drop in a budget into excel on one sheet, then on another sheet put in a drop down bar that changes the month of the year it automatically pulls in my detail budget from the referenced month.

My problem is that in two separate categories I have the same description, but different GL.

GL	Description	Actual	Budget
50460	Locks & Keys	50	500
50350	Locks & Keys	50	300

<tbody>
</tbody>

My problem is that my formula is referencing the name to match and not to GL, but when I change it to match the GL it doesn't work.

=IFERROR(INDEX('Budget Drop'!$B$4:$AZ$499,MATCH(B81,'Budget Drop'!$B$4:$B$499,0),MATCH($A$2,'Budget Drop'!$B$3:$AZ$3,0)),0)

So, because I have two separate GL's with the same name, it won't automatically drop in the budget amount because it doesn't know which one to pull.

I hope this makes sense and I hope you can help!

 

[Aladin Akyurek]

I was going to try that if the above didn't work first and it worked. Thank you for your help!

You are welcome. It's a robust lay-out for VLOOKUP...

 

[Tetra201]

Let A1:D3 house the sample you posted.

E1: 50460

F1:

=VLOOKUP($E1,A:D,MATCH("budget",INDEX(A:D,1,0),0)

would return the budget value.

No need for a hasty formula that has to be adapted.

The working alternative to the formula from Post #7 is as follows:

=IFERROR(VLOOKUP(A81,'Budget Drop'!$A$3:$AZ$499,MATCH($A$2,'Budget Drop'!$A$3:$AZ$3),0),0)

Same requirement: please make sure entries in A81 and in 'Budget Drop'!$A$4:$A$499 are of the same kind.