Index + match assistance!

[LemonOrange]

Good day!

Please save me from scratching my eyes out! There are two parts:

1st:

I am trying to get the interesting value of two inputs with the index match function. The formula is in H35 and is looking up the value from H31 (which the user can input) and the value from H33 which can be inputted.

I understand the value can't be returned because the same value is repeated throughout the table. How do I tell excel to take that value from the row that was inputted (H31)

2nd

I would like H37 to be the index of B28:AA28 from the intersecting value.

I hope I have been clear enough? Any help would be greatly appreciate. Please see my workbook below. Thank you in advance! !

[ConneXionLost]

Given your example of "G" and "0.22", what values do you expect to see for the "Intersect" and "New P Group"?

[newdoverman]

I have included your workbook with what I think that you are after. If I understand correctly, you are wanting to translate from one pressure group to another. If that is correct, I'm not sure which way you are wanting to go so I chose 1 to see if that is what you want.

[LemonOrange]

Hi NewDoverMan!

That is exactly the idea that I am looking for however, I would like the user to be able to type in a different pressure group to begin with. I had something similar to what you wrote but it only works if you manually put the pressure group into the formula.

I tried to work around this by first getting the intersect (just a cell reference) of the pressure group and time.

Then using that intersect position/value in a formula to find the New Pressure Group at the bottom which relates to the intersect.

Any other suggestions?

[LemonOrange]

Hello Conne,

Sorry for the late reply, do you have any other suggestions?

[newdoverman]

Change the formula in the worksheet that I uploaded to this:

Please Login or Register  to view this content.

The group in H31 can now be changed and the value in J37 will change accordingly.

If you change the formula to the following, it can be moved to where you have your existing formula in H37:

Please Login or Register  to view this content.

[LemonOrange]

New DoverMan to the rescue! Again!

I had tried the INDIRECT previously but my syntax was wrong.

Thanks again, keep well.

[LemonOrange]

Just out of curiosity - how does the anchoring affect things. I don't see the difference? eg $B$28:$AA$28

[newdoverman]

You're welcome.
The absolute references in this case just allow you to make changes to the formula where it is then move it to where you want it to be.

[LemonOrange]

I have copied and pasted the formula into my workbook but it is not working? I tried creating it manually. Is there something I am missing here?

Sorry to be a pest...

[ConneXionLost]

How about this:

Please Login or Register  to view this content.

Cheers,

[LemonOrange]

It's working, thank you!

[newdoverman]

The difference between the workbooks was I used Named Ranges (which I use a lot) which are absent from your workbook.

I should have told you about using Named Ranges.

If you select your chart A2:AA27, and click on Define Names, Left column the names will be created and the formula will suddenly work.