table lookup

P

pm

Still struggling with my lookup formula. The table below is an example of
what I am trying to accomplish. I am getting inconsistent results. I
concatenated column H, and I into L. My goal is to find the vendor match in
the table with column L. And if it matches populate column J with the
corresponding Location from the table in column D. I am using the following
formula in J: =LOOKUP(L3,C$1:D$321,D$1:D$321). In this example the 1st and
2nd row pulled the right location. But in the 3rd row it pulled the wrong
location - from the row above. I appreciate any help!


H I J L
999050539 FORT BEND COUNTY JP#2 LOC1 999050539FORT BEND COUNTY JP#2
999120266 JIM WELLS COUNTY JP1 LOC2 999120266JIM WELLS COUNTY JP1
999075446 JP 1 WILLIAMSON COUNTY LO16 999075446JP 1 WILLIAMSON COUNTY

Vendor Table:
C
D
999050539FORT BEND COUNTY JP#2 LOC1
999120266JIM WELLS COUNTY JP1 LOC1
999074481MONTGOMERY JP #5 LO16
999075446JP 1 WILLIAMSON COUNTY LOC4
 
N

Niek Otten

LOOKUP requires your table (col L) to be sorted ascending.
Use VLLOKUP instead. Look here for a tutorial:

http://www.contextures.com/xlFunctions02.html

Pay special attention to the 4th argument of VLOOKUP; don't omit it.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Still struggling with my lookup formula. The table below is an example of
| what I am trying to accomplish. I am getting inconsistent results. I
| concatenated column H, and I into L. My goal is to find the vendor match in
| the table with column L. And if it matches populate column J with the
| corresponding Location from the table in column D. I am using the following
| formula in J: =LOOKUP(L3,C$1:D$321,D$1:D$321). In this example the 1st and
| 2nd row pulled the right location. But in the 3rd row it pulled the wrong
| location - from the row above. I appreciate any help!
|
|
| H I J L
| 999050539 FORT BEND COUNTY JP#2 LOC1 999050539FORT BEND COUNTY JP#2
| 999120266 JIM WELLS COUNTY JP1 LOC2 999120266JIM WELLS COUNTY JP1
| 999075446 JP 1 WILLIAMSON COUNTY LO16 999075446JP 1 WILLIAMSON COUNTY
|
| Vendor Table:
| C
| D
| 999050539FORT BEND COUNTY JP#2 LOC1
| 999120266JIM WELLS COUNTY JP1 LOC1
| 999074481MONTGOMERY JP #5 LO16
| 999075446JP 1 WILLIAMSON COUNTY LOC4
|
|
|
|
|
|
|
|
 
P

pm

Hey Markus,

Thanks for the suggestion. I am now getting #N/A in my location field...any
suggestions?
 
M

Markus Fischer

Are your records starting at line 1? If not you might want to adjut the
C$1:D$321 part of the formula. Column D should contain the locations and
column L the formula.

Let me know if it works.
 
P

pm

My first record starts at line 2, so I changed the formula to C$2:D$321.
Column D = locations and column L = formula.....results are still #NA
 
M

Markus Fischer

Where do you records start in colum H:L?

Basically on cell J2 it should be :
=VLOOKUP(L2,C$1:D$321,2,FALSE)
cell J3:
=VLOOKUP(L3,C$1:D$321,2,FALSE)

And so on...
 
P

pm

Start on row 2 - formula as follows:

=VLOOKUP(L2,C$2:D$321,2,FALSE)
=VLOOKUP(L3,C$2:D$321,2,FALSE)
=VLOOKUP(L4,C$2:D$321,2,FALSE)

Could it be a formatting issue? formatted as General. thanks.
 
M

Markus Fischer

Could you check that the strings you are comparing are exactly the same? You
would get N/A if for instance one of them had a space at the end and the
other not.

In that case, use the TRIM function to remove unnecessary spaces.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hlookup help 6
two table lookup match 12
Sum / Lookup 11
Index Match Vlookup? 23
league table sort 0
#div/0! Error 3
Lookup in a matrix (with x and y headings) based on value x and y 2
lookup and return column number 2

Top