Index function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to use the index function. Sheet1 has
ColA: ColB:
AS ALISON SWEET
JS JAMIE STAR
LN LISA NEWMAN
MJ MARIA JONES
MP MELISSA PAUL
TN TARA NELSON
VS VICTORIA SAMPSON

Sheet2 ColF:
AS
MP
VS
LN
MJ
MP
TN
AS
JS
LN
MJ
MP
TN
VS
AS, etc all the way down

What is the formula to turn ColF into the full names listed in ColB by using
ColA as the reference for ColF? I need it to start with =index(-, match(
or something like that.
 
One way:

Assume the sample list in Sheet1 is in A1:B7

In Sheet2

Assuming the initials are in F2 down

Put in G2: =VLOOKUP(TRIM(F2),Sheet1!$A$1:$B$7,2,0)
Copy down

Or perhaps with an error-trap added,

Put in G2:
=IF(ISNA(MATCH(TRIM(F2),Sheet1!$A$1:$A$7,0)),"No
matches",VLOOKUP(TRIM(F2),Sheet1!$A$1:$B$7,2,0))
Copy down

Initials in col F without matches will return: "No matches"

Note: Above presumes the initials in Sheet1 col A are unique
 
There is no formula to *turn* column F into the names from sheet1.
You could use an adjoining column, using column F as the lookup value, so
that this adjoining column will display the corresponding names from sheet1.

Data list with names and initials on sheet1:
A1 to B7

Sheet2, initials start in F1,
Enter this formula in G1:

=INDEX(Sheet1!$B$1:$B$7,MATCH(F1,Sheet1!$A$1:$A$7,0))

And drag down to copy as needed.

If you have a long list in column F, you could simply *double* click the
fill handle of G1, after you entered the formula, to *automatically* copy
the formula down column G, as far as there is data in column F.
 
Glad to hear that !
Treat the suggestion as yet another option then <g>
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Melissa said:
Thanks, but I figured it out, I used
Click to expand...
=INDEX(Sheet1!B:B,MATCH(F1,Sheet1!A:A,0))
 
THE ONE I DID WORKED =INDEX(Sheet1!B:B,MATCH(F2,Sheet1!A:A,0))

AND THAT ONE YOU SUGGESTED WORKED TOO,
=VLOOKUP(TRIM(F1),Sheet1!$A$1:$B$7,2,0)

I JUST COPIED AND PASTED WHAT YOU PUT CHANGING IT TO F1 INSTEAD OF F2 AND IT
GAVE ME THE SAME RESULT

THANKS!!

SORRY FOR USING ALL CAPS
 
Back
Top