A1 is any word
A2 is blank
A3 = A1
A4:A10 = random words
C3:J3 = Reference words
$C4-10:$J4-10=Random words
What I need is when I type in a word from A4:A10 into the Cell A1, the
cell A3 becomes that cell and it makes cells C3:J3 the corresponding
values of the same row as the A4:A10 value

For example
IF I type Reference into A1, I need it to look like this

Reference Test No HA DING (etc)

Reference Test No HA DING (etc)
Blank
Moop
Red
Interesting
Juggling
Nine
Ten



Can anyone help me?


--
fluci
------------------------------------------------------------------------
fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=393190

One way ..

Put in A3: =IF(A1="","",A1)
(Revise the formula a little ..)

Select C3:J3

Put in the formula bar:
=IF(A3="","",VLOOKUP(A3,A4:$J$10,{3,4,5,6,7,8,9,10 },0))

Array-enter the formula, i.e press CTRL+SHIFT+ENTER

C3:J3 will return the desired results

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"fluci" wrote in
message ...

A1 is any word
A2 is blank
A3 = A1
A4:A10 = random words
C3:J3 = Reference words
$C4-10:$J4-10=Random words
What I need is when I type in a word from A4:A10 into the Cell A1, the
cell A3 becomes that cell and it makes cells C3:J3 the corresponding
values of the same row as the A4:A10 value

For example
IF I type Reference into A1, I need it to look like this

Reference Test No HA DING (etc)

Reference Test No HA DING (etc)
Blank
Moop
Red
Interesting
Juggling
Nine
Ten



Can anyone help me?


--
fluci
------------------------------------------------------------------------
fluci's Profile:
http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=393190

Put in the formula bar:
=IF(A3="","",VLOOKUP(A3,A4:$J$10,{3,4,5,6,7,8,9,10 },0))

A typo "correction" for the table array cell reference ..
and a slight revision to add TRIM() for robustness ..

Put instead in the formula bar with C3:J3 selected:

=IF(A3="","",VLOOKUP(TRIM(A3),$A$4:$J$10,{3,4,5,6, 7,8,9,10},0))

and array-enter as before

Another better but slightly longer alternative which returns say, : "-" for
any unmatched cases instead of ugly #N/As [where the input in A1 doesn't
match with what's in A4:A10]

we could put in the formula bar with C3:J3 selected:

=IF(A3="","",IF(ISNA(MATCH(TRIM(A3),$A$4:$A$10,0)) ,"-",VLOOKUP(TRIM(A3),$A$4
:$J$10,{3,4,5,6,7,8,9,10},0)))

and array-enter as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

im really new at using excel
what do i 'revise' the formula with?


--
fluci
------------------------------------------------------------------------
fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=393190

max that is fantastic
however... it only seems to work for C3
i dont understand it
how do i change it to work for cells D3:J3?


--
fluci
------------------------------------------------------------------------
fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=393190

Thank you very much max!
I was playing around with the formula and figured out it works if I
removed one of the columns in the VLOOKUP part of the code.

For example, C3 says
=IF(A$3="","",IF(ISNA(MATCH(TRIM(A$3),$A$4:$A$101, 0)),"-",VLOOKUP(TRIM(A$3),$A$4:$J$10,{*-3-,-*-4,5,6,7,8,9,10},0)))

Then D3 says
=IF($A$3="","",IF(ISNA(MATCH(TRIM($A$3),$A$4:$A$10 1,0)),"-",VLOOKUP(TRIM($A$3),$A$4:$J$10,{4,5,6,7,8,9,10},0 )))

And so on all the way to J3

Thanks alot max!


--
fluci
------------------------------------------------------------------------
fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=393190