Formula for returning a value

  • Thread starter Thread starter marty
  • Start date Start date
M

marty

Hi,

I have a column on my work sheet, all the numbers are
seven digits. I need a formula to consider just the
first 3, to return a value from a VLOOKUP sheet.

any help much appreciated
 
One way

=INDEX(Sheet2!$B$1:$B$20,MATCH(TRUE,ISNUMBER(1/(FIND(LEFT(TRIM(B3),3),Sheet2
!$A$1:$A$20)=1)),0))

the latter entered with ctrl + shift & enter and originally posted by Harlan
Grove


you might get this to work

=VLOOKUP(LEFT(TRIM(B3),3),Sheet2!$A$1:$B$20,2,0)

or

=VLOOKUP(--LEFT(TRIM(B3),3),Sheet2!$A$1:$B$20,2,0)

depending on the format but the first formula is much more robust



--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
Back
Top