Hi I just joined up and couldnt see my problem replicated on the site and was just wanting a bit of formula advice,



I'm using a VLOOKUP and maybe should be using a different function or need help alterting to give the result I want when more than one instance of the lookup occurs with polar opisite results that need to be a single value.

I've uploaded a copy of my spreadsheet with most of the data taken out (so you can see only the entries I'm having an issue with)

I've got a speradsheet that I keep a list of purchase orders on, that I manually update the values for each order as they have payments or changes are made to them. on the attcahment its called COST DATA SHEET LOOKUP TABLE.

Before I manually update my purchase order entries I download and paste a report onto the the Latest SQ01 Dumps page. This then activates the formulas to check the data against my manual entries (which I then review and update after I confirm the changes are correct). It conditional formats any cells that don't match to highlight as red for me. And the checks come back as TRUE or FALSE. Each line on the SQ01 Dumps page details a purchase order number and a item on each line from that order.


Its the CELL Y3 on my cost data I would like help with. Its purpose is to lookup on the download dump page if an purchase order number is OPEN or CLOSED.

in the cell is the code:


Now if you look at my download dump page you will see theres only 2 lines of data, which both apply to line 3 on cost data sheet.
Now both lines apply to the same purchase order, one line is item 1 the other is item 2 on the same order. My problem is that one item is CLOSED the other is OPEN.

Now my check using VLOOKUP on cell Y3 returns OPEN or CLOSED based on which item is on the higest row in the data. iF open comes first its open if closed comes first its closed.

I would like my check in Y3 to review each line and if theres at least a single occrance of OPEN and 1 or more instantces of CLOSED. then I want this cell Y3 to display OPEN.

Is there a way to weight the OPEN value above the closed one so it always shows when one of the rows has an open order?

Maybe its more an IF solution possibily.

If you could paste the code I need to change or download my excel docuement and change the forumula for me in Y3 on the cost data page and re-upload it with a response that would be brilliant.


Thanks in advance for any help

Attached Files

2015 Cordell Valuation Lookup Tables for excel help forum.xlsx (41.9 KB, 11 views)

Here's one way...

A bit of creative criticism, I would rename your tabs to something more concise ideally without spaces. This will make your formulas a bit more aesthetic. For example, if your tabs were Validation, Dumps, and Lookups, the formula then would be:

Thanks I'll try it out when I get a chance

thanks it works.

FYI the ">0" part can be skipped
=if(COUNTIFS(Dumps!A:A,Lookups!A3,Dumps!O:O,"Open" ),"Open","Closed")

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post