If I have two cells on Sheet 1 (cells A1 & A2) Where A1 is where Id enter
someones IQ score and A2 would represent their percentile rank. Sheet 2 is
a two column table w/ all possible IQ scores (Column A) and Percentile Ranks
(Column B).

My question, is it possible to use a lookup function to look up a percentile
rank (from sheet 2). For example, if I enter an IQ of 130 in cell A1 (sheet
1) and excel would look up the corresponding Percentile Rank (sheet 2) and
then bring this Percentile Rank value forward to sheet 1 and autopopulate
cell A2.

"it seemed much more clear when it was just in my head..."

Thanks in advance.

Steve

In Sheet1,
In A2: =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Shek5150" wrote:
If I have two cells on Sheet 1 (cells A1 & A2) Where A1 is where Id enter
someones IQ score and A2 would represent their percentile rank. Sheet 2 is
a two column table w/ all possible IQ scores (Column A) and Percentile Ranks
(Column B).

My question, is it possible to use a lookup function to look up a percentile
rank (from sheet 2). For example, if I enter an IQ of 130 in cell A1 (sheet
1) and excel would look up the corresponding Percentile Rank (sheet 2) and
then bring this Percentile Rank value forward to sheet 1 and autopopulate
cell A2.

Thanks Max...that did the trick.


"Max" wrote:

In Sheet1,
In A2: =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Shek5150" wrote:
If I have two cells on Sheet 1 (cells A1 & A2) Where A1 is where Id enter
someones IQ score and A2 would represent their percentile rank. Sheet 2 is
a two column table w/ all possible IQ scores (Column A) and Percentile Ranks
(Column B).

My question, is it possible to use a lookup function to look up a percentile
rank (from sheet 2). For example, if I enter an IQ of 130 in cell A1 (sheet
1) and excel would look up the corresponding Percentile Rank (sheet 2) and
then bring this Percentile Rank value forward to sheet 1 and autopopulate
cell A2.

Is it possible to make the end result cells calculate? I am doing something
similar in a mileage log for staff. Based on the client selected, a number of
miles is returned from a list. Since this is a form template that will be
filled out by staff, I copied the formula to about 10 rows so the fields will
automatically populate. My problem is that I need all of these mileage
totals to sum but am getting #N/A and so my column will not total. Is there
a different way to do this so that I will be able to sum the mileage total
column?

Thank you!!
Christi

"Max" wrote:

In Sheet1,
In A2: =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Shek5150" wrote:
If I have two cells on Sheet 1 (cells A1 & A2) Where A1 is where Id enter
someones IQ score and A2 would represent their percentile rank. Sheet 2 is
a two column table w/ all possible IQ scores (Column A) and Percentile Ranks
(Column B).

My question, is it possible to use a lookup function to look up a percentile
rank (from sheet 2). For example, if I enter an IQ of 130 in cell A1 (sheet
1) and excel would look up the corresponding Percentile Rank (sheet 2) and
then bring this Percentile Rank value forward to sheet 1 and autopopulate
cell A2.

Welcome. Please press the Yes button in that response, thanks.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Shek5150" wrote:
Thanks Max...that did the trick.

Easier to arrest it at source by using IF(ISNA(...),0, .. error traps
to return zeros for any unmatched cases

Downstream calcs will then not be affected by any #N/As
resulting from unmatched cases

So you could use something like this instead:
In A2:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1, Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Christi" wrote:
Is it possible to make the end result cells calculate? I am doing something
similar in a mileage log for staff. Based on the client selected, a number of
miles is returned from a list. Since this is a form template that will be
filled out by staff, I copied the formula to about 10 rows so the fields will
automatically populate. My problem is that I need all of these mileage
totals to sum but am getting #N/A and so my column will not total. Is there
a different way to do this so that I will be able to sum the mileage total
column?

Thank you!!
Christi

That's perfect! Thank you!!!!

"Max" wrote:

Easier to arrest it at source by using IF(ISNA(...),0, .. error traps
to return zeros for any unmatched cases

Downstream calcs will then not be affected by any #N/As
resulting from unmatched cases

So you could use something like this instead:
In A2:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1, Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Christi" wrote:
Is it possible to make the end result cells calculate? I am doing something
similar in a mileage log for staff. Based on the client selected, a number of
miles is returned from a list. Since this is a form template that will be
filled out by staff, I copied the formula to about 10 rows so the fields will
automatically populate. My problem is that I need all of these mileage
totals to sum but am getting #N/A and so my column will not total. Is there
a different way to do this so that I will be able to sum the mileage total
column?

Thank you!!
Christi

Welcome. Could you take a moment to press the Yes button in that response?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Christi" wrote:
That's perfect! Thank you!!!!