extracting data 1

[stoke]

hi there,

i have a unique identifier in a1,sheet2 and need to extract the related data from sheet 2 - ie find in column a then extract related data in column b to b2,sheet2 then related data in c2 and so on.......

i have this:
=IF(ISNA(VLOOKUP(F2,datarange,2,FALSE)),'YTD RM 2002-2003'!F2,VLOOKUP(F2,datarange,2,'YTD RM 2002-2003'!F2))

and it's not returning the right results..........

thanks guys,

 

[substitute]

Your lookup formula is mixed up:

=IF(ISNA(VLOOKUP(F2,datarange,2,FALSE)),'YTD RM 2002-2003'!F2,VLOOKUP(F2,datarange,2,'YTD RM 2002-2003'!F2))

the red bit should just be FALSE shouldn't it?

My interpretation of the above is:

"If the value in F2 on this sheet is not found in column 1 of datarange return the value of F2 from the YTD RM sheet, otherwise return the value in column 2 of datarange" Is that what you intended it to mean?

It would help if you could say where datarange is and which cell the formula is in! The formula as it stands bears little relation to the problem you expressed in terms of a1, b1, sheet 2 etc.

 

[stoke]

apologies, i'll try again.
i need to lookup a1(sheet 2) on sheet 1. when found return the relative values on sheet 1, ie b2,c2,d2, to the same cells on sheet 2.

i have this formual which works well to extract the data from b1 sheet1 to b1 sheet2.
=IF(ISNA(VLOOKUP(A1,FleetRMreport,2,FALSE)),"No Match",VLOOKUP(A1,FleetRMreport,2,FALSE))
where FleetRMreport is the data in columns a and b on sheet 1.

so i need ot be able to do the same across the horizontal, but still using a1 sheet2 2 as the unique identifier.

hope i have explained myself better!
Thanks for your time and help.

 

[substitute]

Your formula is OK as far as it goes. So you make "datarange" cover the whole area of interest in the "lookup" sheet (columns a,b,c,d...). Then use the same VLOOKUP formula in each column of the "result" sheet but increase the third argument by 1 for each column you move to the right, so in column B you use 2, column C you use 3 etc.

To help when copying the formula around use $A1 not A1 so it always looks up on column A. Also you could replace the third argument by COLUMNS($A1:B1) so the formula "knows" which column number it is in and so which column of datarange to copy from. (enter the above in column B of the "result" sheet then copy to columns c, d, e etc)

Does that do it?

 

[stoke]

outstanding.
you're a scholar and a gentlemen.
i particularly liked the COLUMNS($A7:E7) part - great stuff.
Thanks again.

 

[stoke]

Following on from this i have a situation where i have:
Column P
=IF(ISNA(VLOOKUP($A3,datarange,COLUMNS($A3:G3),FALSE)),"-",VLOOKUP($A3,datarange,COLUMNS($A3:G3),FALSE))

works perfectly. however when i set the formula for:
Column Q
=IF(ISNA(VLOOKUP($A3,datarange,COLUMNS($A3:M3),FALSE)),"-",VLOOKUP($A3,datarange,COLUMNS($A3:M3),FALSE))

Column Q is only correct if a) there is data to extract and b) if there was no data returned in column P.

if, however there is a return in column P, and there is nothing for column Q to return.........i get a result of 0.00

I hope i have explained this well enough, and would love to know why the results vary and i don't get '-' where i am getting 0.00.

Thanks, very much!

 

[substitute]

I'm wondering if you have the COLUMNS references right.

The formula in P returns the 7th column (ABCDEFG) of datarange while that for Q returns the 13th column (ABCDEFGHIJKLM). The reason for using COLUMNS with the mixed absolute/relative reference is to yield a value for the VLOOKUP column argument that marches across the datarange as the lookup formula marches across the destination range. Therefore if the formula in P3 uses COLUMNS($A3:G3) then I would expect the formula in Q3 to use COLUMNS($A3:H3), i.e. one column to the right.

If you are getting a lookup result of 0.00 then it seems to be coming from the 13th column of Datarange. I can't think of a formatting or other setting that could produce 0.00 out of "-". I can't explain the apparent linkage to the value returned in P unless there is a relationship between the values in columns 7 and 13 of datarange.

 

[stoke]

Thanks for your input, the COLUMNS part is correct, (the data retrieval extracts 1 column, then next, misses x columns and repeats.
also, where the COLUMNS reference retrieves the correct data where it can - just the issue one of 0.00 instead of '-'.

i have also tried the formula without COLUMNS and just uses the column reference instead - the result remains the same.
It is not just that column either, the same situation occurs right the way to column AW.

This is very bizarre, but many thanks for your help and time, i guess i will just have to live with this as it is.

Cheers,

 

[substitute]

The only other thing I can think of is that if you extract a reference to a blank cell the extract returns zero (0.00 to 2DP). To avoid ths you need to use something along the lines of

if(isblank(vlookup(...)),"blank looked up",vlookup(...))

and note that blank means completely empty, the empty string "" is NOT blank!

If you are finding a blank where there appears to be no blank then maybe the definition of DATARANGE is wrong or there is another matching value in the range.

I would do a few auditing things, like remove the if(isna(),"-",) bit and leave the raw vlookup to see where the ISNA is cutting in, use MATCH instead of VLOOKUP to return the row found rather than the data in the row and then visually inspect the row, etc.