Need help with a lookup on multiple identical values please

[dgarber]

Hi Everyone,

I have an imported worksheet where there are multiple duplicate descriptions in Column A that have different values in Column G that I need to do a lookup on.
The first set of lookup's work fine as expected but it seems to me like I read a way to do this on the forum and I'm not finding it?
Example is in A6 I have a description of CONV REG 10% ETH and I do a lookup for a value in Column G in the Invoice worksheet. Then in A19 I have the same description
of CONV REG 10% ETH and I need to lookup the value in Column G in the Invoice worksheet.

Any help would be great.

Thanks,
Dean

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[dgarber]

AliGW is that better?

[AliGW]

Much - thanks!

[FlameRetired]

Have you tried assigning index numbers to the qualifying items and then passing those via INDEX to column G? This would be an array formula.

Upload a workbook that represents what you are working with and what you want. Instructions for doing this are at the top of this page (gold banner).

[dgarber]

Hi Dave,
The problem I run into is the invoices are not static. We receive anywhere from 1-3 products per invoice so for example if its a single product it always resides in A14 and A18.
If there are 2 products everything is shifted down one row so what was in A18 is now in A19 and if 3 products shifted down 2 rows so what was in A18 is now in A20.

Thanks,
Dean

[FlameRetired]

Have you tried a dynamic named range? Then assign index numbers relative to that named range.

=if(Condition=DNR, ROW(DNR)-MIN(ROW(DNR))+1)

That should address the challenge of changing ranges and indexing assignments.

If you are unaware of how to build a dynamic named range (DNR) upload a small representative (with dummy data) workbook. I'll show you my favorite method.

Happy Veteran's Day.