find code# from sheet 1 on sheet 2, compare value on sheet 1 with value on sheet 2

I've used excel, but not to accomplish anything like this, so a newbee. I know excel can do this, but no idea how. The first step is finding wrong code# for the values listed on Sheet 1. The values for the codes on sheet 1, must be between the values listed on 2nd sheet with the same code.

code# is a series of number & letters (example: 1AAX231)
code will always be in col P and value will always be in col X
This is what I want to do: Take the code and value from 1st sheet (2 cells) find the code on 2nd sheet and compare 1st sheet value to =>,=< values on 2nd sheet.
I'd like to be able to paste a query page onto the 1st sheet of the workbook

use code# on 1st sheet col P and value from 1st sheet col X
find matching code in 2nd sheet col G
check the value from 1st sheet against the values on the 2nd sheet col H & col J.
The value from 1st sheet must be =or > than col H & =< than col J
The value on 1st sheet has to be between the 2 values listed on 2nd sheet. If it isn't then I'd like to highlight the code# on the 1st sheet (col P) or return False in col Q (anything that indicates it's the wrong code will do)
[do I need to take Col H and Col J on 2nd sheet and make them one entry/one cell?]

Hi

why not just use a formula
query list!Q2: =IFERROR(AND(X2>=VLOOKUP(P2,'code list'!F:G,2,FALSE),X2<=VLOOKUP(P2,'code list'!F:G,2,FALSE)),"Code not on second sheet")

Is it right that the example file doesn't have any correct entries?

rylo

Originally Posted by rylo

Hi

why not just use a formula
query list!Q2: =IFERROR(AND(X2>=VLOOKUP(P2,'code list'!F:G,2,FALSE),X2<=VLOOKUP(P2,'code list'!F:G,2,FALSE)),"Code not on second sheet")

Is it right that the example file doesn't have any correct entries?

rylo

No, the col 1 (green) on sheet 1 has some incorrect codes, most are correct.
There are normally 300 rows per sheet.

Sheet 2 codes are the correct code.

I can't see what's wrong with your formula - Duh!
It returns 'wrong code' for every entry.
[I changed your formula 'code not on second sheet' to 'wrong code'.

(All codes are on the 2nd sheet)
On 1st sheet - query list - the first code is wrong - the 2nd one is correct.

Hi

I'll blame mondayitis...

query list!Q2: =IFERROR(AND(X2>=VLOOKUP(P2,'code list'!F:G,2,FALSE),X2<=VLOOKUP(P2,'code list'!F:H,3,FALSE)),"Code wrong")

FALSE means it is wrong
TRUE means it is right
Code wrong means the code is incorrect.

rylo

I've got Sunday/Mondayitis and working on Tues.

could you put that on the sample workbook I attached and attach it so I can see how you did it?

Hi

Here 'tis.
892684.xlsx

rylo

Sorry for not getting back with a whopping big THANK YOU!
I had used the formula on my copy of the wkbk that I sent to you. Worked fine. When I tried to use it with a new query list and the full code list, it didn't work. I thought if I saw your wkbk, it would give me the answer. -Nope.
Finally got back to working on this last night. Convinced it had to be something to do with the new query lists or the new code list. Could not find any logical reason why it wasn't working. I started checking things that couldn't possibly matter. Gave up, got up this morning, and took another shot at it.
Had myself convinced it had to be something in the formatting on the number cols or the code cols. Finally figured it out. The new 'query sheets' had spaces after the code. Take out the spaces and everything works fine. Can't believe it took me so long to figure out that the codes were added with blank extra spaces. (no idea how, why – don't care – just taking them out)
Thank you for your patience and your help. Learned more from looking at your formula than I did from reading 'VLOOKUP help' for hours.
I'm going to take a shot at the next part of this, if I get stuck, I'm going to come looking for you! No idea if you know how much you helped me – but, you saved me hours of time. Thanks again, much appreciated.