# Look up & auto populate between sheets

S

#### Shek5150

If I have two cells on Sheet 1 (cells A1 & A2) Where A1 is where Iâ€™d enter
someoneâ€™s 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..."

Steve

M

S

C

#### Christi

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

M

M

#### Max

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
xdemechanik

C

#### Christi

That's perfect! Thank you!!!!

Max said:
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
xdemechanik

M

#### Max

Welcome. Could you take a moment to press the Yes button in that response?
--
Max
Singapore
http://savefile.com/projects/236895