Retrieving values based on matching values on separate worksheets

Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.

Example:

On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.

I have attached a sample spreadsheet for clarification.

Edit - refer to post #3 for attachment.

Can you provide some sample results? Can you highlight where you've inserted these sample results manually so we can spot what you're after?

Based on the sample set, I think a better "match value" is the SR40, SR41, etc.

Place this formula in C5 and then copy down and across the table:

=INDEX(CSRD!$E$17:$BB$33, MATCH($A5, CSRD!$C$17:$C$33, 0), MATCH(C$4, CSRD!$E$12:$BB$12, 0))

Originally Posted by JBeaucaire

Based on the sample set, I think a better "match value" is the SR40, SR41, etc.

Place this formula in C5 and then copy down and across the table:

=INDEX(CSRD!$E$17:$BB$33, MATCH($A5, CSRD!$C$17:$C$33, 0), MATCH(C$4, CSRD!$E$12:$BB$12, 0))

Unfortunately, the SR40, SR41, etc. is not hardcoded and should be a result of the lookup (if that makes sense). Those references need to be dynamic as the lookup_value will change depending on the data that requires analysis, i.e. it can change from V. Good to good, to low, etc, in which case it wont always be SR40, SR41, etc.

The end use of this will be a bar chart so hardcoding the SR values is not an option as it will leave long gaps within the graph.

Hope that makes sense.

In C4 and copied across row 4:

=INDEX(CSRD!$E$12:$BB$12, MATCH($B$4, CSRD!$E$9:$BB$9, 0) + COLUMN(A$1) -1)

In C5, then copied down and across the table:
=INDEX(CSRD!$E$17:$BB$33, MATCH($A5, CSRD!$C$17:$C$33, 0), MATCH($B$4, CSRD!$E$9:$BB$9, 0) + COLUMN(A$1) - 1)

Glad to help.

NOTE: Just use the QUICK REPLY box below to carry on normal conversation. Unnecessary use of the QUOTE button just clutters the thread and makes it hard to read. Cheers.