question on assigning value to cell based on array

**canadave** · 05-04-2011, 10:21 AM

Hi,

Just a simple one (well, nigh unto impossible for me, but probably simple for the gurus here!) that I'm hoping someone can help me with

In Excel I'm doing some fictional sports gaming stuff (for cricket, actually). I have a list of players, and to each player I have to assign a "Batting Card Value" that is based on their real-life cricket batting average.

I have a chart that shows which Batting Card Value should be assigned if the player has a certain real-life batting average. I've translated the chart into an array in Excel. For instance, if a player has a batting average of 3.00, then their Batting Card Value should be "1". If the batting average is 4.50, then the BCV should be "2". And so on.

The issue is that there are 50 possible Batting Card Values (between 1 and 50), each with a corresponding batting average on the chart.

Now, I want to calculate a cell on a different sheet (but for simplicity's sake let's pretend it's on the same sheet), that looks at the value I input for the player's batting average (say in cell "C1"), compares it to the batting averages on the array (from the chart), and returns the appropriate matching BCV to the cell D1.

I could of course use 50 nested IF statements in cell D1 ("IF(C1=3.00,1,IF(C1=4.50,2,......,IF(C1=77.00,50))))))"), but I'm sure there has to be a more elegant way

Can anyone tell me what that would be?

**ConneXionLost** · 05-04-2011, 11:11 AM

Assuming the first column of your array is the batting average, and the second is the BCV, then you could use the VLOOKUP function to get your result.

Cheers,

**canadave** · 05-04-2011, 09:40 PM

Originally Posted by ConneXionLost

Assuming the first column of your array is the batting average, and the second is the BCV, then you could use the VLOOKUP function to get your result.

Cheers,

Yes, that worked! Thank you

I was unaware of the VLOOKUP function...neat!

Now I have one more question, if you don't mind. I have several columns, whose content I need to concatenate into one text string. The columns are:

1) a name;
2) a 2-digit number;
3) a one-digit number with a plus or minus sign in front of it;
4) a two-letter string;
5) the VLOOKUP'ed number; and
6) another text field.

When I use the CONCATENATE function for 1 and 2, it works. Same for 1, 2, and 3. And also 1, 2, 3, and 4. But when I add column 5, the string disappears and all I get is "#N/A".

I assume this is because Excel is not able to concatenate a calculated field? Is there any way around this? I tried using the TEXT function to convert the calculated VLOOKUP to a text string for use in the concatenation, but that didn't seem to work (or maybe I just didn't do it right--in any case, I got the same result.)