table lookup

[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$321,D$1$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

 

[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$321,D$1$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
|
|
|
|
|
|
|
|

 

[Markus Fischer]

As suggested change to
"=VLOOKUP(L3,C$1$321,2,FALSE)"

 

[pm]

Hey Markus,

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

 

[Markus Fischer]

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

Let me know if it works.

 

[pm]

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

 

[Markus Fischer]

Where do you records start in colum H:L?

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

And so on...

 

[pm]

Start on row 2 - formula as follows:

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

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

 

[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.