Trying to do a lookup and not having any luck

[Rick K]

Hello all!

I've become, thanks to you guys, pretty good at figuring out match/index/vlookup/sumifs stuff, this one's got me stumped - probably because of a text/numeric issue. Attached is a small spreadsheet, where I have an invoice and a dollar amount associated with it. There's a small data table next to it, with invoices, amounts, and a text value. I'm in need of a formula that will look at the invoice number, it's amount, then go over to the data table, find the same invoice number and amount, then put the corresponding text value in the cell. Thought I had it a few times, but keep getting errors.

Bear in mind that invoices often will repeat themselves, but have different amounts. It's not a concern if an invoice repeats itself and has the same dollar value. Those are valid, and it's assumed that the same text value would be returned in those instances.

Any suggestions?

THANKS
Rick

[JohnTopley]

Try

=IFERROR(INDEX($P$2:$P$21,MATCH($C3&$E3,$N$2:$N$21&$O$2:$O$21,0)),"")

Enter with Ctrl+Shift+Enter

[Ace_XL]

In J3

=IFERROR(INDEX($P$2:$P$21,MATCH(1,INDEX(($N$2:$N$21=C3)*($O$2:$O$21=E3),0),0)),"")

Copy down

[Olly]

In J3, use array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Jonmo1]

Try this in J3

=IFERROR(LOOKUP(2,1/(($N$2:$N$21=C3)*($O$2:$O$21=E3)),$P$2:$P$21),"No Match")

[JohnTopley]

Incorrect reply: my apologies.

[Rick K]

Works great... THANK YOU!!

[Jonmo1]

You're welcome.