Wrong data type

[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!

 

[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!

 

[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.