Index Match on non absolute value

[dache416]

Hello,
I need you someone’s help regarding my index match formula. The formula is trying to match the items in column B, however the items in column B is non absolute value.

Thank you,
Cheryl

[Sam Capricci]

with an index match the 0 at the end of match means exact, a 1 means less than and -1 means greater than.
is this what you are wanting?
Lookups have more variability as in that the lookup can place something within a range (as can a vlookup).
so if using a vlookup or lookup you have a value of 5.222 and want to return the nearest value like 5.200 or 5.225 then maybe a lookup or vlookup will work for you.
without more information it is difficult to determine what formula to recommend or how to change it.
You haven't even posted your formula or sample data. A sample sheet might go a long way to getting a better answer. Be sure to include a representative sample AND expected results.

[dache416]

Here's the spreadsheet.

[Sam Capricci]

add this to your formula that is returning the #N/A
=IF(-E3=Cost,"1674",IF(-E3=Gain,"4312",IF(-E3=Income,"4302")))*1 to your account column (column B in the JVU tab), then the account description will return the values you want.
the reason it is returning an error is because when you put the quotes around 1674 (etc) you are changing them to text while the references are numeric.

[johnmpl]

Hi, dache416!

The problem with your formula is you are looking text in a range of numbers.

You can try:
1. Change your if formula in column B, putting the numbers without quotation marks (just leave the number) like:
=IF(-E3=Cost,1674,IF(-E3=Gain,4312,IF(-E3=Income,4302)))

or

2. Change your Index Match formula:
=INDEX(LBOAcctDes,MATCH(--B3,LBOAcctNo,0))

There are another options... but with these two is quite enough. Blessings!

[dache416]

Problem solved. Thank you very much Sambo kid, and johnmpl

[Sam Capricci]

you're welcome, glad we could help!

And if you are so inclined you can thank ANY and ALL those who stopped by to help by clicking on "*Add Reputation" under their posts as that is how we advance on this forum.