Excel match query

  fermerboy 22:33 11 May 04
Locked

Hi
What I'm stuck on is this, I have a table 5 columns by 5 rows and I want to specify which column to use and then match down in in that column to give me the row number of the match. The table is in the range A1-E6(including headings, so =MATCH(A10,A1:E1,0)gives me the column number that I want by inserting the column heading in cell A10. What I want then is to look down that column for a match with cell A12 to give the row number as answer. I have tried match again but you have to say the range which changes based on the first answer.Is there another way of telling excel which column to look for a match? There is probably something really easy that I'm missing.
Thanks in advance

  VoG II 22:37 11 May 04

Yep, saw this earlier (and indeed your earlier, earlier) post and I must confess that I don't know the answer.

Do you program in VBA for Excel because I think that's the way to go. If so, is it just the row number that you want to return?

  fermerboy 22:46 11 May 04

Thanks VoG
Unfortunately I don't program VBA.
If I get the row number I can use the index function to look up the answer I need in another table. The same answers appear in all the columns but are in different rows each time so by choosing the column I choose which order is "correct" and therefore allocate a score. Vlookup does it but it doesn't allow me to choose the column to look down. My sheet is more complex than the example.
I'll maybe get it yet. Its to mark a competition.
Thanks

  VoG II 23:17 11 May 04

ALT+F11 to start the Visual Basic Editor.

Insert/Module

Paste in

Function FindIt()

Application.Volatile

Dim colm, rw

Dim iRow As Integer, iCol As Integer

For iCol = 1 To 5

If ActiveSheet.Cells(1, iCol) = ActiveSheet.Range("A10").Value Then

colm = iCol

Exit For

End If

Next iCol

For iRow = 2 To 6

If ActiveSheet.Cells(iRow, colm) = ActiveSheet.Range("A12").Value Then

MsgBox (iRow)

Exit For

End If

Next iRow


End Function

Then X to exit the vbe and in a cell enter

=findit()

Does this give the right answer?

  fermerboy 00:15 12 May 04

Thanks VoG
That does work, Don't understand any of it though!!!!!
I have been pursuing this off and on for about a week since you gave me the link to the excel tips spreadsheet.
I posted on another forum as well with not a lot of response, but I checked back and this was suggested.
=MATCH(A12,OFFSET(A1,,MATCH(A10,A1:E1,0)-1,6),0)
It seems to work well and combined with a index formula further over gives me a result. I looked at all the lookup functions but never thought of using that. Must admit I'm getting into excel now.
Wish I was as bright as you guys though!!! I'm going to tick this and not waste any more of your time.
Thank you very much.

This thread is now locked and can not be replied to.

Samsung Galaxy Book review: Hands-on with Samsung’s high-end hybrid

1995-2015: How technology has changed the world in 20 years

This is probably one of the best bank ads we’ve seen

Apple Watch 2 vs Huawei Watch 2 comparison review: watch out for 4G smartwatches Apple