vlookup sh1 to sh2

[Frank Malone]

Please help me get the vlookup working
sh1
a4 ticker b4 company c4 ranking d4 % Of value
a5 AA
a6 ALL
a7 ALTR
a8 AMAT
a9 AMGN

sh2
a1 company b1 ticker c1 ranking d1 % of value
a3 aloca AA B+ 0.31%
a28 Allstate ALL B+ 0.30%
a29 Altera ALTR B 0.08%
a31 Applied Materials AMAT B- 0.34%
a34 Amgen AMGN B 0.77%

I enter this in b5 =VLOOKUP(aa, sheet2A2:A34, 1)
Please help me with what's wrong

 

[JudithJubilee]

The string you are looking for, eg. aa need sto be in the
first column of the lookup array (sheet2A2:A34). also
your array is wrong.

You need to rearrange the Sheet 2 data so that the Ticker
column is at the first column. Your formula will then
look like this:

In cell B5:
=(A5,Sheet2!$A$3:$D$34,2,FALSE)
In cell C5:
=(A5,Sheet2!$A$3:$D$34,3,FALSE)

You need an ! for sheet names,. $ signs fix the formula
so theat when you drag down the vlookup will look at the
same data. The array is the whole of the data A334. For
company the data is in the 3rd column. The FALSE gives
you the #N/A message in the cell when it cannot find an
exact match.

Hope this helps
Judith

 

[Guest]

Opps!
Put vlookup in front of both of these!!

Judith

 

[Frank Malone]

The formula is working except I had 2 symbols that are not in sheet2 but I
never got the #N/A on these in sheet1. It just got the symbol next to it on
both of the missing symbols. Anymore help on this would be appreciated.
Thanks very much.

 

[JudithJubilee]

You need to add the word FALSE at the end

In cell B5:

 

[Frank Malone]

Judith you had formula correct I just failed to enter $A$ I entered $A
Thanks so much I needed this formula very much, you really helped me.