Formula to show the value from the Cell Below as per the lookup value

[prkhan56]

Hello All,
I am using Excel 2010
I have attached a sample of my problem.
I want a formula to be typed in Cell C1 (colored for sake of explanation) and drag it across then it should show the respective values below that name in Col D, Col E, Col F etc
Eg: Lookup value is in C1 which should be matched in Col A and when dragged across should show the respective values
The value will change every time in the colored cell (see After Sheet)
Hope this is clear
TIA
Rashid

[JohnTopley]

Try this

=IFERROR(INDEX($A$2:$A$12,SMALL(IF($A$2:$A$12>=$B$2,ROW($A$2:$A$12)-ROW($A$2)+1),COLUMNS(($A$1:A1)))),"")

[oeldere]

=VLookup(B2,After!$C$1:$G$1,column()-2,0)

[Tony Valko]

Not real sure what you're wanting to do.

Maybe this entered in D1 and copied across:

=INDEX(Before!$A:$A,MATCH($C1,Before!$A:$A,0)+COLUMNS($D1:D1))

[Simon3]

If the values will be strictly numbers see if this works.

=IFERROR(IF(MATCH(After!C$2,Before!$A$2:$A$12,0),MATCH(After!C$2,Before!$A$2:$A$12,0)+1,),"")

Lookup Problem - Excel Forum.xlsx

[prkhan56]

Thanks to John, Tony & Simon.
It works great. You are all great help.

Oeldere your formula only works if the value entered is 1. But for other values it shows #N/A.

[Tony Valko]

You're welcome. We appreciate the feedback!

[Simon3]

If you index the value if true section it will work with any values. (see below)

=IFERROR(IF(MATCH(After!C$2,Before!$A$2:$A$12,0),INDEX(Before!$A$2:$A$12,MATCH(After!C$2,Before!$A$2:$A$12,0)+1,1),),"")