Help with VLookup

[BPhilb]

If got the following cell:

=VLOOKUP(A2&K1,errors,3, FALSE)

I'm trying to read to colums (A2 = store number and K1 = Error Code). The worksheet errors has the store number broke down by multiple codes listed vertically and I'm trying to bring them into another worksheet horizontally. I think I'm close but I keep getting a value of #NA. Any suggestions would be much appreciated. Thanks.

 

[firefytr]

Hello there,

Do you understand how VLOOKUP works? It will take the value to search for (first syntax) and look in the left-most column of your array (second syntax) returning the specified column number of said array (third syntax) while keeping a match type you specify (fourth syntax). So, if the left-most column of errors range does not have concatenated values of column A and K, then you'll never find a match.

Can you give us some more details about your data and it's structure? Such as, what does errors refer to? What values are in A2 and K1? What are the values in the range errors? Can you give an example of 5-10 rows, giving us the expected results?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[BPhilb]

I'm mostly versed in Access and have not worked with VLookup's until today. I've been very frustrated because it seems simple. The spreadsheet is at work unfortunetly but here's what I'm looking at:

Sheet 1

Store Returned Errors % TA TB TC
10 100 10 10 #NUM

Sheet 2

Store Code Units
10 TA 3
10 TB 7

The #NUM is where I'm trying to get the VLookup to match store 10 to the TA code on sheet 2 (the cells are named errors) and the value of 3 is what I'm wanting to populate. This goes on for around 400 stores. I hope this makes sense. Thanks for your assistance.

 

[GlennUK]

Change Sheet2 to be:
LookUpVal Store Code Units
10TA 10 TA 3
10TB 10 TB 7

which solves the problem Zack has predicted. You can create this column very easily by inserting a new column and using a formula such as =B2&C2 to generate the required field.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[djj55]

Is it true that the lookup table must be sorted on the key column for the vlookup to work correctly?
djj

 

[firefytr]

It helps, otherwise you could get unexpected results. If you have duplicates it has a large impact. It's always best to have your data sorted in Excel, most algorithms (formulas, etc) work best on pre-sorted data.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[BPhilb]

Thanks so much guys. That made a lot of sense.

 

[KenWright]

Always worth looking at the FAQs first

How does VLOOKUP work??
faq68-4743

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------