Need Help (VLOOKUP)

[Robert Lie]

Dear All,

I need help regrading vlookup.
I have table like below:

A B C D E F
BALE NO. No METTLER HEARSON METTLER HEARSON
=========================================================
LB104105990040 1 14.40 14.40 12.95 13.02
LB104122785046 2 15.04 10.69
LB104130680035 3 12.56 11.05
LB104001674036 4 13.53 11.15
LB104106180642 5 14.17 11.72
LB104130586035 6 12.27 12.82 10.36 11.03
LB104005079039 7 16.00 13.09
LB104122784049 8 14.18 11.90
LB104015090039 9 14.72 10.69
LB104122791031 10 13.93 14.07
LB104122790034 11 14.57 14.60 10.30 11.16
LB104105993044 12 14.53 13.06
LB104083133044 13 15.51 13.57


I want to return METLLER value based on BALE NO.
How to write the VLOOKUP formula if I want to return METLLER value with
x first characters of BALE NO, like below

LB104083 15.51


Thanks

Robert Lie

 

[Biff]

Hi!

You have 2 columns each for both Mettler and Hearson. How do you determine
which column to use?

The lookup sample you posted: LB104083, has data in only one Mettler column
but what if the lookup value was LB104105 which has data in both Mettler
columns?

If you need to lookup data from both Mettler's or Hearson's then you need to
make each unique. Maybe like this: Mettler1, Mettler2, Hearson1, Hearson2.

Assume your table is on Sheet2 in the range A1:F14.

On Sheet1 you have in:

A1 = LB104083
B1 = Mettler
C1 = formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(Sheet2!C2:F14,MATCH(TRUE,ISNUMBER(SEARCH(A1,Sheet2!A2:A14)),0),MATCH(B1,Sheet2!C1:F1,0))

Biff