REOPENED Lining up row of numbers with column of numbers using INDEX MATCH

[dougkpga]

I have lists of numbers

LIST 1 LIST 2 LIST 3 LIST 4 LIST 5 LIST 6 LIST 7 LIST 8 LIST 9 LIST 10 LIST 11 LIST 12 LIST 13 LIST 14 LIST 15 LIST 16
1 1 3 2 7 6 3 2 4
1 2 2 2
1 3 3 3
1 4 4
1 5
1 6 6
1 7 7
1 8
1 9
1 10

So I would like the numbers in row 1 to be in the same row as in list 2 as you can see in Lists 10 to 3.

I tried VLOOKUP but the numbers remained in the Top Row not dropping to the position opposite the matching number in List 2.

VLOOKUPS
LIST 1 LIST 2 LIST 3 LIST 4 LIST 5 LIST 6 LIST 7 LIST 8 LIST 9 LIST 10 LIST 11 LIST 12 LIST 13 LIST 14 LIST 15 LIST 16
1 1 3 2 7 6 3 2 4 3 2 7 6 3 2 4
1 2 0 0 0 0 0 0 0
1 3 0 0 0 0 0 0 0

This was the formula I used

=VLOOKUP(B14,$B$14:$I$23,2,FALSE)

I uploaded the sheet.

I would appreciate any help please. TIA


EDIT - Sorry the copy and paste didnt work, I lined them all up but it didnt show when I posted - however it shows properly on the spreadsheet.

[popipipo]

Please Login or Register  to view this content.

I think this formula will work.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[dougkpga]

Hi Ali

I hope the title is better now.

Thanks Doug

[dougkpga]

Hi Popipo

Thanks for the formula .

I am not sure where to paste it.

I tried it in B2 and J2 but it didn't work.

Cheers doug

[AliGW]

Unfortunately, it hasn't changed. (yet).

[dougkpga]

I put the new title in the Reason for editing duhhh

[AliGW]

Much better - thanks! you have no idea how many thousands of threads are about VLOOKUP.

[AliGW]

Try this:

=INDEX($J$2:$P$11,MATCH($B14,$B$2:$B$11,0),MATCH(J$13,$J$1:$P$1,0))

[dougkpga]

Vlookup might not even be the answer I guess.

[AliGW]

It isn't - see above.

[dougkpga]

Hi Ali

which cell do I paste the formula in please?

[popipipo]

Hi Popipo

Thanks for the formula .

I am not sure where to paste it.

I tried it in B2 and J2 but it didn't work.

Paste the formula in J14:P14

[dougkpga]

I think I will need the adjusted spreadsheet as all I get is #NAME? in all the cells

[popipipo]

I think Change the comma in semi comma.

[dougkpga]

Hi popipipo

This is what I am getting from your sheet when it is supposed to look like the pale yellow area.

Thanks for trying.

[popipipo]

Sorry but is misunderstood your question.
Use the formula AliGW provide you

[Fluff13]

You don't have the maxifs function which is why you get #NAME?

Did you try Ali's formula from post#9?
Just paste it in J14, then fill down & across.
If you don't want the 0s to appear you could use

=IFERROR(1/(1/INDEX($J$2:$P$11,MATCH($B14,$B$2:$B$11,0),MATCH(J$13,$J$1:$P$1,0))),"")

[dougkpga]

Hi Fluff

I tried Ali's formula but she didn't tell me which cell to paste it as I asked in a reply - I will try with your info.

Absolutely worked perfectly.

Thanks to Ali for the initial formula, thanks to you where to paste and the modification and thanks to popipo.

I'm very grateful for the help.

Cheers Doug

reps to be added

[Fluff13]

You're welcome & thanks for the feedback.

[AliGW]

Sorry - I was marking end of year exam papers! Glad you got sorted. It's unlike me to forget to say where to put a formula - sorry about that.

[dougkpga]

Hi again,

Sorry to open this again but I'm having a problem applying the formula to other cells

=INDEX($J$2:$P$11,MATCH($B14,$B$2:$B$11,0),MATCH(J$13,$J$1:$P$1,0))

In the formula it is matching J2:P11 to the cell in B14.

Where it needs to match the cell in B2.

Also does the formula need to include the header (where it says MATCH(J$13,$J$1:$P$1,0)) as i need to apply it to data without a header.

I will colour code what I mean.

The formula seems to be linking the green coloured cells rows 1 to 11 to the green coloured cells rows 14 to 23.

So how would I connect the blue coloured cells with the green coloured cells?

So from rows 26 to 35 I tried this formula to do this ...

=INDEX($C$26:$I$35,MATCH($B26,$B$26:$B$35,0),MATCH(J$26,$J$26:$P$35,0))

and copied that across and down.

But as you can see it gave me zeros and didnt match the numbers in row 26 opposite the number in column B.

Sorry for the extra advice.

Thanks Doug

[AliGW]

You need to provide an updated sample workbook that shows the issue, otherwise we are just guessing.

[dougkpga]

Modified sheet attached

[AliGW]

So how would I connect the blue coloured cells with the green coloured cells?

I can't work out from the new workbook what it is you are trying to achieve. What is the ultimate aim? At the minute, all we have is the same set of data repeated three times down the worksheet.

Please fill in MANUALLY what you want to see - never mind the formula, just show us what you want. Also, explain in WORDS what the formula is meant to do (the logic).

[dougkpga]

Thanks Ali

I need to match the the numbers in columns C D E F G H I

With numbers in column B

The top green and orange box is just a sample - the green coloured cells are the raw data

and the orange box is the desired outcome.

The formula you gave me works but it doesn't match the data in columns C D E F G H I rows 14 to 23

to J14 to P23.

Hope this explains better.

Thanks again

[AliGW]

Ah - the penny has dropped!!!

I'm just about to eat - will have a look after supper.

[Fluff13]

Is this what you mean?
In J26 copied down & across
=IF(AND($B26=ROW(A1),C$26=ROW(A1)),ROW(A1),"")

[dougkpga]

Thanks so much!!!

[AliGW]

Too late ... !!!

[Fluff13]

Hadn't seen post#26, when I replied before and was based on rows, this is based on col B
=IF($B26=C$26,$B26,"")