Vlookup pulling wrong data

[Evapar18]

I am confused as to why my Excel 2016 is pulling the wrong data. I think I have the formula set up correctly.

IF(G4="2A",VLOOKUP(H4,C17:K30,9),IF(G4="3A",VLOOKUP(H4,M17:U30,9)))

FYI - I have noticed other spreadsheets where I have a Vlookup in the cell is failing. It forces me to type in the exact number for it to look up instead of using a linked cell.

Hopefully, this link will allow you to download an example of the problem I am having.
https://drive.google.com/file/d/1Rqa9u-N8fNNW6m0oeUhuMjRnViVpn5lc/view?usp=sharing

I have two classes of threads 2A and 3A. I have an "If" statement to determine which table to look at. Then its supposed to look up the "Diameter - Threads per inch" and give me the Minor Diameter.

Sincerely,
EP18

 

[Joe4]

Welcome to the Board!

You are not using the 4th optional argument of VLOOKUP. That is the indicator whether or not to allow approximate matches, if an exact match is not found.
If you leave it off, it defaults to allowing approximate matches. Approximate matches only work correctly if your data is sorted accordingly.

Try changing your formula to:

IF(G4="2A",VLOOKUP(H4,C17:K30,9,0),IF(G4="3A",VLOOKUP(H4,M17:U30,9,0)))

and see if that makes a difference. If it returns "#N/A", then it means it cannot find a match.
See here for more details: MS Excel: How to use the VLOOKUP Function (WS)

Also, if you are copying this cell down rows, you will probably need to lock your lookup range references.

 

[Evapar18]

That fixed it "0".

I guess an "exact" match means it must be exactly the same, not just the value we see?

 

[Joe4]

I guess an "exact" match means it must be exactly the same, not just the value we see?

Correct. It looks at the actual value in the cell, not the format that may be applied to the display.
So if you are dealing with fractions, you may need to round them off so both the value you are looking up and the list you are matching it to have the same number of decimals.

 

[Evapar18]

Let me ask you this. How come when I am looking for a "10-32" thread I have to set the Vlookup to "0" (like we talked about earlier). When I use the same vlookup, but I am looking for ".250-20" I have to use "1" instead? Makes it hard to use the same table and Vlookup to find the minor diameter of each thread type.

Thanks,
EVP18

 

[Evapar18]

Let me ask you this. How come when I am looking for a "10-32" thread I have to set the Vlookup to "0" (like we talked about earlier). When I use the same vlookup, but I am looking for ".250-20" I have to use "1" instead? Makes it hard to use the same table and Vlookup to find the minor diameter of each thread type.

Update (I didn't catch this when I first posted this reply) when I use "1" it gives me an answer, but its the wrong answer. When I use "0" I get "N/A", why? The value its looking for is .025-20. the array lists .250-20. Do I need my value to show the same zero locations? I am confused I don't recall having this much trouble with excel formulas' in the past.

Thanks,
EVP18

 

[Joe4]

Update (I didn't catch this when I first posted this reply) when I use "1" it gives me an answer, but its the wrong answer. When I use "0" I get "N/A", why? The value its looking for is .025-20. the array lists .250-20. Do I need my value to show the same zero locations? I am confused I don't recall having this much trouble with excel formulas' in the past.

As I mentioned, "0" only returns EXACT matches. "1" will return approximate matches, if none if found.
So, if you do not have a perfect match, "0" will return "N/A" (indicating no match), while "1" will return the closest match (of sorted data). If you data is not sorted, you will really get some unexpected results.

Note that when using "0", the match MUST be exact, meaning:
- The value you are looking up and the list you are matching to MUST be the same data type (comparing "numbers" to "numbers entered as text" will NOT work)
- For text values, they MUST match exactly. ".25" does NOT match ".250". And an extra space in either will also not match (i.e. ".25" does NOT match ".25 ").

So, if you see an "N/A" when you use "0", it means that the values do not really match (even though it might appear that way, at first glance, if you do not dive into the details).

 

[Evapar18]

I think that's the problem. I am using text in my array because when I use .250-20 it defaults to a date. I tried several ways to keep that from occurring, but the only way around it was to add the ' in front of the value. Changing my CONCATENATE to support ".000" then copying and pasting just the value ".250-20" seems to work now.