Match question -wanting to match a cell in a one spreadsheet and to be able to return a value for the same row but different column

[Thomas36]

Hi,
I have a very large spreadsheet with about 1000 rows and 30 columns.
To simplify, It would be really helpful if I could fill in spreadsheet 2 with information from spreadsheet 1. However in reality, in spreadsheet one - the fruit column is not the first column on the very large spreadsheet and there may be about 20 columns between the fruit column and the corresponding score for that fruit.
I have been trying to look this up and it seems that I need to do "double match". I cannot work out how to do this. I cannot use index as I do not know which cell the fruit would be in. Also when I have found the fruit, I cannot scroll across to find the score as in reality it is about 20 columns away (same row)

Can anyone help with a formula to fill in H5, 6 and 7 on spreadsheet 2 using info from spreadsheet 1 that I can then apply to my larger spreadsheet?
Thank you

 

[Marcílio_Lobão]

Thomas36, Good morning.

Did you try use a VLOOKUP function?

Spreadsheet 2 --> H5

=VLOOKUP(G5, Sheet1!B5:U15, 20, 0)

The range Sheet1!B5:U15 is a range with all your columns and 20 is the column where is a desired result.

Adapt it to your reality.

Is that what you want?

I hope it helps.

 

[Thomas36]

Hi Marcilio,
Thanks a lot, this sounds like it would work the only thing I am not sure is what the number 20 relates to? As the columns are letters not numbers?
Thanks

 

[Tupe77]

Why you can't use index?

 

[Marcílio_Lobão]

Tupe77, Good morning.

I prefer to use VLOOKUP because it is a more concise and easy to observe the parameters used.

In the case of using the INDEX and MATCH functions it would look like this, using the example presented:

=INDEX(Sheet1!$B$5:$C$15; MATCH(G5;Sheet1!$B$5:$B$15;0); 2)

Adapting the range to reality of user.

I hope it helps.

 

[Marcílio_Lobão]

Thomas36,

"...the only thing I am not sure is what the number 20 relates to? As the columns are letters not numbers? ..."

In your message you said:
"...Also when I have found the fruit, I cannot scroll across to find the score as in reality it is about 20 columns away (same row)..."

20 is how many columns after column 1 your answer must be found.

I hope it helps.

 

[Tupe77]

Thomas said he can’t use the index and I’m wondering why. So it was not my intention to comment on the solution you proposed.

In my opinion, Index (match ()) is much more versatile and therefore better, but that's just my opinion.

I would have changed your formula

=INDEX(Sheet1!$C$5:$C$15; MATCH(G5;Sheet1!$B$5:$B$15;0); 1)

Because the best feature of Index & Match combination is that there is no need for the Index and Match areas to interact with each other.

 

[Fluff]

From the OP's description, I'm thinking it needs something like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Veg	Animals	Fruit	Trees	Fish	Score	Result	Rank
2			Apple			15
3			Mango			14				pear	13
4			Pear			13				apple	15
5
Main

Cell Formulas
Range	Formula
K3:K4	K3	=INDEX($A$1:$H$24,MATCH(J3,INDEX($A$1:$H$24,,MATCH("Fruit",$A$1:$H$1,0)),0),MATCH("Score",$A$1:$H$1,0))

 

[Thomas36]

Hi,
Thanks thats all been very helpful, i tried a view variations and did some more research, I have managed to get it to work with a function called xlookup and found a helpful instruction online of how to do it. I can use it across different spreadsheets too.

 

[Fluff]

Glad you sorted it & thanks for the feedback.