Formula over two pages

[phil2006]

My data sheet is too large to fit on one sheet as there are not enough columns. Is it possbile to have a formula that reads from both sheets? The formula that has been working for one sheet is:

=((VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))


Any help would be great!!

Thanks

[Muhammed Rafeek M]

I am not sure what you want, but here i am giving some solution according to
my understanting:
=IF(ISERROR(VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE),VLOOKUP(B3,sheet3!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE),VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))

If you are not satisfied, pls do mail to mohdraf@hotmail.com with your
workbook.



"phil2006" wrote:

>
> My data sheet is too large to fit on one sheet as there are not enough
> columns. Is it possbile to have a formula that reads from both sheets?
> The formula that has been working for one sheet is:
>
> =((VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))
>
>
> Any help would be great!!
>
> Thanks
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=562811
>
>

[phil2006]

It's not entirely what I meant. My problem is that I have a distance chart with more places along the top row than there are columns. Thus the distance chart must be over two sheets. Whn I type two places into the original sheet the distance is read using the above formula. I now need to edit the fromula so that it can read data from both distance sheets.

Thanks

[SimonCC]

If I understood correctly, you can try:
=IF(ISNA(MATCH(D3,sheet2!$A$1:$IK$1,0)),VLOOKUP(B3,sheet3!$A$1:$IK$400,MATCH(D3,sheet3!$A$1:$IK$1,0),FALSE),VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))

This is assuming that your second data sheet is in Sheet3 and you have the
same number of columns in data as Sheet2. If not, just adjust the reference
accordingly.

Also assuming that value in D3 is always in either of the two datasheets.
If that's not true, you'll need to nest another if to handle situations when
D3 doesn't match anything in the datasheets.

Lastly, make sure the first column in Sheet2 is also in Sheet3 so the
VLOOKUP will work when looking up in Sheet3.

-Simon

"phil2006" wrote:

>
> My data sheet is too large to fit on one sheet as there are not enough
> columns. Is it possbile to have a formula that reads from both sheets?
> The formula that has been working for one sheet is:
>
> =((VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))
>
>
> Any help would be great!!
>
> Thanks
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=562811
>
>