Table Array Search

[ExcelJunkie]

Hi:

See attached image. If the end user puts in the year "1974", I'd like a function to search the entire table, reference whatever it finds in the same row, but always refer to column H. So, as per attached image, if end user put in 1974, the output would "Tiger." If someone entered 2006, the output would be "Dog."

How would I write this function?

Thank you!

[Bryan Hessey]

Hi:

See attached image. If the end user puts in the year "1974", I'd like a function to search the entire table, reference whatever it finds in the same row, but always refer to column H. So, as per attached image, if end user put in 1974, the output would "Tiger." If someone entered 2006, the output would be "Dog."

How would I write this function?

Thank you!

You need the Un-Index function, I don't think there is one yet.

Assuming that your table is in A1:J12, and that the submission goes to K1, in L1 (of your table sheet) put

=IF(ISERROR(MATCH($K$1,B1:J1,0)),"",$K$1)

formula fill this to row 12, then in M1 put

=OFFSET(A1,MATCH(K1,L1:L12,0)-1,0)

and voila, 'Tiger' for '1974'.

---

[ExcelJunkie]

Works great! You rock! Thank you so much!

[clayv]

Alternative with no helper cells...

Put following in L1 (assumes input cell is K1):

=IF(ISNA(OFFSET(A1,MATCH(K1,OFFSET(A1,0,MATCH(K1,B1:J1,1),3,1),0)-1,0)),"Not Found",OFFSET(A1,MATCH(K1,OFFSET(A1,0,MATCH(K1,B1:J1,1),3,1),0)-1,0))

- Clay Ver Valen
Excel Help