Create Search Box that returns four different responses

[Rabbit]

Sorted by zip code right? Just add 1/2/3 to the row returned by the function.

@Neo, in post #12, they said they're on a new environment that doesn't have Access.

[NeoPa]

Thanks Rabbit. I interpreted that to mean they were temporarily at the wrong type of computer (IE. One without Access), rather than fundamentally without access to Access (as it were). Anyway, that seems to explain my confusion :-)

[sperling1349]

Rabbit, thanks! That worked, for a while. The problem I ran into was with my formula - I had the zip in B2 and in C2 I had the formula that returned the correct city based off the zip, D2, E2 returned the rest of my info based off the previous cell (C2 off B2, D2 off C2, etc.) This worked until there was more than one city name that the zip returned (since D2 was based off the City in C2 it found the first instance of that city, and then E2 returned off D2). I'm having fun doing this so I will figure this out, with your guys' help of course.

Question is (I think) How do I do a index/match formula that can return the next adjacent cell, and also the next row down? Is that possible? This is the formula that returns the next cell over and returns the correct info

=INDEX(All!E2:E 165453,MATCH(B2 ,All!A2:A165453 ,0)+4)

How do I add to that so that I can return the next row down? I want to be able to have cell C3 return the next instance of the correct city based off the zip input in B2, and then D3, E3, F3 return the next instance down from the the previous cells. I hope this makes sense, I'm almost confused!

Row 2 does exactly what I want: zip is typed in B, C returns city, D returns state, E days, F carrier. In row 3 I want to return the next instance of that zip from B2(sheet is sorted by zip so the next line down has the next instance). Is there a way to do that, along with moving to the right cell by cell?

Thanks for all the help.

[Rabbit]

To get the next row down, add one to the row returned by match.

index(range of data, match() + 1)

To get the next column, use the optional column parameter.

index(range of data, match(), 2)

[sperling1349]

Can I use those together in the same function? (match()+1,2)?

[NeoPa]

Yes indeed. They are merely offset values for the row and the column respectively. The resultant values in there determine how far to go up/down and left/right. Don't omit the [range of data] parameter though ;-)