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]

What are you trying to look up from that sample of 3 rows?

 

[joshie78]

Well, currently it is pulling the budget amount based on the description but because I have the same description it won't pull, but it won't work when I try to look it up by GL.

 

[Aladin Akyurek]

Well, currently it is pulling the budget amount based on the description but because I have the same description it won't pull, but it won't work when I try to look it up by GL.

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.

 

[Tetra201]

Well, currently it is pulling the budget amount based on the description but because I have the same description it won't pull, but it won't work when I try to look it up by GL.

Your formula is looking for the description in 'Budget Drop'!$B$4:$B$499 -- right?
Do you have a column with GL numbers in sheet 'Budget Drop'? If yes, are the column entries actually numbers on number-looking text strings?

 

[joshie78]

Yes my GL is in column A on where I drop my budget into the worksheet and is a number 50460. Will it only reference text?

 

[Tetra201]

Yes my GL is in column A...

Then I guess you formula should be:

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

... Will it only reference text?

No, but please make sure entries in A81 and in 'Budget Drop'!$A$4:$A$499 are of the same kind -- say only actual numbers. Or only text strings.

 

[Aladin Akyurek]

Yes my GL is in column A on where I drop my budget into the worksheet and is a number 50460. Will it only reference text?

Did you try the VLOOKUP set up, adapted to your data?

 

[joshie78]

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

 

[Tetra201]

You are welcome.