VLOOKUP not looking up?

[Cyberpawz]

Please Login or Register  to view this content.

This is the code I have in cell Q11, it is looking in AG3 as a reference point. Now it is querying in the middle of the Archive sheet instead of the beginning. This works on the drop down menu, but it doesn't on the fields on the External NCMR sheet. I'd like to know why, if someone can explain it to me and tell me what would work it be greatly appreciated. I have a feeling I may have to use a script like this, but I want to verify first.

Please Login or Register  to view this content.

I just want to make sure first, because if I do it's fine, but I'd of thought VLOOKUP would of been smart enough to do what I wanted...

[Cutter]

Use this: =IFERROR(INDEX(Archive!$C:$C,MATCH($AG$3,Archive!$D:$D,0)),"")

VLOOKUP() looks for a match and retrieves values to the right of that match. You are retrieving values to the left of the match.

[Cyberpawz]

Ok, it works, sorry I was doing a generic error, called typo... thanks a lot

[tigertiger]

Try the formula for Q11:
=IFERROR(INDEX(Archive!$B$2:$F$999,MATCH($AG$3,INDEX(Archive!$B$2:$F$999,0,3),0),1),"")

plz mention the number in red color for respective column you need

[Cyberpawz]

Why are there two red then?

[Cyberpawz]

Use this: =IFERROR(INDEX(Archive!$C:$C,MATCH($AG$3,Archive!$D:$D,0)),"")

VLOOKUP() looks for a match and retrieves values to the right of that match. You are retrieving values to the left of the match.

I've tried your script, the problem I am running into is that I am doing both. Date Filed is to the right, so I thought this would still work:

Please Login or Register  to view this content.

It doesn't. Can you please explain why?

[Cutter]

Your VLOOKUP() is looking for the value found in AG3 withing the B column of your Archive sheet. It is actually in the D column.

This formula finds the date found in column F:
=IFERROR(VLOOKUP($AG$3,Archive!$D$2:$F$999,3,FALSE),"")

[tigertiger]

Why are there two red then?

=IFERROR(INDEX(Archive!$B$2:$F$999,MATCH($AG$3,INDEX(Archive!$B$2:$F$999,0,3),0),1),"")

3 for 3rd column (of data Archive!$B$2:$F$999) in data for match AG3

1 for get the 1st column (of data Archive!$B$2:$F$999) for result