Wrong data type

D

Dale Long

This is my first time to post a question, so I'm not sure what this
will look
like or if this will look like a spreadsheet.

A value located in columns 2,3, or 4
will identify a value in column 1.
I am trying to solve this problem. The individual formulas
appear to work OK, but when put together
it comes up with '#VALUE!' which according to the tag
is "wrong data type". What is my problem??
How do I solve it??
Given
4 (Column)
Z (Row)
#VALUE! (Answer)
D (Should be answer)

1 2 3 4
A ^ @ ?
B % ! >
C $ P <
D # X Z

2 $D$21:$D$24
3 $E$21:$E24
4 $F$21:$F$24

'=OFFSET($F$24,0,-($B$13-1)) = D

'=INDEX($D$21:$F$24,(4),(3)) = Z

=MATCH($B$15,$F$21:$F$24,0) = 4

'=VLOOKUP($B$13,$C$27:$D$29,2,FALSE) =
$F$21:$F$24

'=OFFSET(INDEX($D$21:$F$24,MATCH($B$15,VLOOKUP
($B$13,$C$27:$D$29,2,FALSE),0),2),0,-($B$13-1)) = #VALUE!
 
J

Jim Cone

You can highlight each function and its arguments in the formula bar
and press the F9 key. That will display the value the function returns.
Start with the right most function and work left.
Fix the function with the bad value.
Do not press enter to exit the formula bar, use the Escape key.
--
Jim Cone
Portland, Oregon USA



"Dale Long" <[email protected]>
wrote in message
This is my first time to post a question, so I'm not sure what this
will look like or if this will look like a spreadsheet.

A value located in columns 2,3, or 4
will identify a value in column 1.
I am trying to solve this problem. The individual formulas
appear to work OK, but when put together
it comes up with '#VALUE!' which according to the tag
is "wrong data type". What is my problem??
How do I solve it??
Given
4 (Column)
Z (Row)
#VALUE! (Answer)
D (Should be answer)

1 2 3 4
A ^ @ ?
B % ! >
C $ P <
D # X Z

2 $D$21:$D$24
3 $E$21:$E24
4 $F$21:$F$24

'=OFFSET($F$24,0,-($B$13-1)) = D
'=INDEX($D$21:$F$24,(4),(3)) = Z
' =MATCH($B$15,$F$21:$F$24,0) = 4
'=VLOOKUP($B$13,$C$27:$D$29,2,FALSE) =
$F$21:$F$24
'=OFFSET(INDEX($D$21:$F$24,MATCH($B$15,VLOOKUP
($B$13,$C$27:$D$29,2,FALSE),0),2),0,-($B$13-1)) = #VALUE!
 
D

Dale Long

Hi Jim

Really appreciate your response. I did as you suggested and it shows that
the Match function when combined with the Lookup function returns #VALUE!.
But I don't have a clue what is wrong. Looks to me that if it is OK on each
single function, it ought to be OK when they are combined.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top