Solved Develop a look up table

Microsoft Excel 2003 (full)
April 26, 2011 at 11:42:48
Specs: Windows XP, 1.0Gb
I have an Excel 2003 spreadsheet that uses "hard" coding, i.e., If x = "Joe Smith" then... This spreadsheet is difficult to update when the names are changed as I need to find every reference to that name in the spreadsheet and then make the needed changes. I want to try something like "if x = $A$1, then ...." I think that the names need to be in a look-up table and "$A$1" would contain the name I need, but can't figure out the syntax for the "If" statement and the correct look-up table format.

Thank you.
Brian W


See More: Develop a look up table

Report •


#1
April 26, 2011 at 12:01:42
✔ Best Answer
I don't quite understand what you are trying to do since Excel doesn't use syntax like If x = "Joe Smith" then....

That looks like a VBA statement not an Excel function.

In any case, read up on the VLOOKUP function in the Excel Help files. VLOOKUP uses a table_array to retrieve data based on the value in a given cell (the lookup_value)

If that's not what you are looking for, come on back with a little more information and we'll see if we can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 26, 2011 at 14:21:42
Dear DerbyDad03,

Thank you for your quick response. I used "hard" copying, i.e., =if(B11 = "Smith", "E",""). With this copy, if I typed "Smith" in the appropriate cell, it would return "E" in the appropriate cell.

What I am trying to do is to eliminate "Smith" and "E" in all of the coding, and replace it with variables (that would mean "Smith" and "E") in a look-up table. With this procedure, I could change the names associated with the variables when needed and not need to search for the names and change them manually within the spreadsheets.
Thank you.

Brian W


Report •

#3
April 26, 2011 at 15:27:59
Not at all sure what it is your trying to do, but try this:

Create a new tab with your names in it, call it MyNames,
it should look something like this:

        A          B
 1) Critera 1   Critera 2
 2)	
 3) ALPHA         ONE
 4) BETA          TWO
 5) GAMMA         THREE
 6) DELTA         FOUR
 7) EPSILON       FIVE
 8) ZETA          SIX
 9) ETA           SEVEN
10) THETA         EIGHT
11) IOTA          NINE
12) KAPPA         TEN

If B11 is the cell where you enter the data,
then in cell C11 enter the formula:

=VLOOKUP(B11,MyNames!A1:B12,2,FALSE)

As long as the Name in cell B11 matches any cell
on sheet MyNames cells A1 thru A12, it will return the corresponding data from column B.

So if in cell B11 you enter the text string: Gamma
Your response in cell C11 should be the text string: Three

If the name you enter in cell B11 does not appear in MyNames, you get an error message.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 26, 2011 at 17:08:39
re: What I am trying to do is to eliminate "Smith" and "E" in all of the coding, and replace it with variables (that would mean "Smith" and "E") in a look-up table.

Which is why I suggested reading up on VLOOKUP. Did you?

Mike has given you an example of how to use it. You should still read the Help file to get a better understanding of how it works.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 27, 2011 at 04:43:14
Thank you for your assistance. I'll give it a try.

Brian W


Report •

#6
April 27, 2011 at 04:44:37
DerbyDad03,

Thank you for your assistance. I'll read about VLOOKUP again now that I better understand it due to Mike's example.

Brian W


Report •

#7
April 28, 2011 at 10:09:54
DerbyDad03 and Mike,

I tried your suggestion and it worked fine for two columns; the problem I now have is that I need four columns.

In spreadsheet WORKS which contains the data (with column headers of LN, FN, TP, SD), I am using A1 to D4. In the entry spreadsheet, I want to enter LN in A6 and get the responses for FN in B6, TP in C6 and SD in D6.

In A6 of the entry spreadsheet, I entered the LN. In B6, I entered =VLOOKUP(A6,works!A1:D4, 2, False) . When I copied this formula with paste special formula into C6, I got =VLOOKUP(B6, works!B1:E4, 2, False) and when I copied this formula into D6, I got =VLOOKUP(C6, works!C1:F4, 2, False) . I got the correct responses, but I don't think the coding is correct.

I thought that the index number should be changed but that didn't work. I also tried using absolute references but that didn't work either.

Thank you for your assistance.
Brian W


Report •

#8
April 28, 2011 at 11:12:56
re: "I got the correct responses, but I don't think the coding is correct."

If you got the correct responses, why don't you think the coding is correct?

If you are trying to lock down the lookup_array so that it doesn't change when you drag, drop or paste the formula, use:

=VLOOKUP(B6, works!$A$1:$D$4, 2, False),

This formula will always lookup your lookup_value in $A$1:$A$4 and return the corresponding value from $B$1:$B$4 since your col_index_num is 2.

If you want to pull values from Column C, the col_index_num should be 3.

Keep in mind that the col_index_num is not the number of the spreadsheet column, but the index number of the column in the lookup_array

In other words, if you used:

=VLOOKUP(B6, works!$F$1:$H$4, 2, False), 2 would refer to Column G

=VLOOKUP(B6, works!$F$1:$H$4, 3, False), 3 would refer to Column H

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
April 29, 2011 at 08:25:58
DerbyDad03,

Thank you for your assistance.

Brian W


Report •

Ask Question