vlookup with similar lookup values

[Dave F]

I have a table of data downloaded from a fixed asset ledger.

I need to find the P&L code for each PO. This is easy enough to do
with VLOOKUP.

However, here's the catch. Some assets don't have associated POs, and
so "NO PO NUMBER" is in their place. The problem is this value "NO PO
NUMBER" can be related to multiple P&L codes.

Is there a way to associate each instance of "NO PO NUMBER" with its
corresponding P&L code?

Assume PO number is in A:A and P&L code is in B:B.

(Yes, using Access is an option here. But I would prefer to do this
analysis in Excel if possible.)

 

[Pete_UK]

You could apply a filter to column A to select NO PO NUMBER. Then in
the first cell visible you could change this to:

="NO PO NUMBER"&

then click on the cell in column B on the same row. You might get
something like:

="NO PO NUMBER"&B83

if 83 is the first visible row. With the filter still active you can
copy this formula down the visible cells to give you the P&L code
tagged on to the comment. You can then remove the filter. Is this the
kind of thing you meant?

Hope this helps.

Pete

 

[Dave F]

Thanks. I ended up solving this problem by doing something somewhat
similar. I created two helper columns, the first being:

=IF(AG2="No PO Number",1,"") and filled down

and the second using:

=IF(AG2="No PO number",CONCATENATE(AG2,SUM(AN$1:AN2)),AG2) and filled
down.

This has the effect of creating NO PO NUMBER1, NO PO NUMBER2, etc.

Then I just copied and pasted the values in the second helper column
into the PO column, and so each row had a unique PO value from which
VLOOKUP could be run.

Circuitous, but it solves the problem.

 

[Pete_UK]

Ah well, I was on the right track, then. Glad to hear you got
something working, and thanks for feeding back.

Pete