Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 410K Members
- 8,139 Online
- 466K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- How to use LOOKUP to refer to tables related to specific ranges of values in a nominated cell

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## How to use LOOKUP to refer to tables related to specific ranges of values in a nominated cell

- Home
- :
- Excel
- :
- General Discussion
- :
- How to use LOOKUP to refer to tables related to specific ranges of values in a nominated cell

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2019 07:42 AM

I want to modify the LOOKUP entry in F16 , * =LOOKUP(ABS(F14),K6:K26,L6:L26)*, (24 shown), so that it refers to and selects the relevant column and value from Tables A, B, C or D according to the value in F8 (24 shown).

I want it to use Table A if F8<25, Table B if F8=25, Table C if F8>25 and <33, Table D if F8>32.

As highlighted, only Table A is referenced, and the value from F14 (24) is 'read' from Table A, W13 (120).

In the example, the value for G8 is 25 and the values should be read from Table B, in H8 the value is 26, so reference should be made to Table C and finally, I8 (33) Table D should be referenced.

For correspondents who play Bridge, the scoresheet allows 4 players to simulate a competitive Teams event and generate a 'Par Score' to compare with the actual Duplicate result at the table - an improvement (I feel), on simply using Chicago scoring. (My Intellectual Copyright)

Thank you,

Alf

Labels:

14 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-02-2019 08:30 AM

Hi,

As the basis you may modify your formula as

=LOOKUP(ABS(F14),OFFSET($K$6,0,0,21),OFFSET($L$6,0,0,21))

With nested IF or another lookup find on how many columns you shall shift your ranges and use that formula instead of second zero in each OFFSET. And be careful with absolute and relative references.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-03-2019 12:49 PM

Just in case a non-standard approach is of interest. I rely entirely upon defined names and never use the standard cell referencing by location. My first step would be to build a defined name 'case' that will indicate which table A-D is to be to used for subsequent lookups.

The definition of 'case'

= 2 + SIGN( combinedHCP-25)+ ( combinedHCP>32)

is a bit of a dog but it sets the scene for the named reference 'table' that is defined by

= CHOOSE( case, tableA, tableB, tableC, tableD )

From there on, one can ignore the fact that 4 tables are involved and simple build the lookup formula as if 'table' were a straightforward range reference. Individual column names can be defined to refer to

= INDEX( table, 0, 1 )

*etc*. as required.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-04-2019 03:53 AM

many thanks, I will look at this.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-04-2019 03:54 AM

Many thanks, This looks very interesting (I like a challenge!), Alf

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-04-2019 08:44 PM

I find it difficult to decipher the applicable formula for you. Please attach your sample Excel file.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2019 06:27 AM

file dropbox link:

As I said, which table is referred to depends on the value in cell row 8 (f,g,h or i)

If the value in cell f8, g8, h8 or i8 is <25 , table A should be used to look up from the corresponding value for row 14 (f to i)

If = 25, then Table B

If in range 26 - 32, then Table C

and if greater or equal to 33, then table D

The choice of which column is used (f, g, h or i) is determined by the user (according to the final Bridge contract and the Bridge vulnerability)

The values in Rows 6, 7, 10 and 12 are entered by the user. Excel determines the 'Par Value' in 16.

the User enters the actual score in 18

Excel calculates the Net score and Looks up the IMPs (Internnational Match Points) equivalent by LOOKUP in Table E

Simple concept! Not so simple to set up to work automatically. I like an intellectual challenge and appreciate the help I am getting.

Alf

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2019 09:51 PM

SolutionHello @Alcesterman ,

In the attached file, I modified K27, V30, AB30, AH30, and AN30 from 40+ to 41. The formula in F16, copied across to I16, is:

=INDEX(($V9:$Z30,$AB9:$AF30,$AH9:$AL30,$AN9:$AR30),

MATCH(F14,CHOOSE(SUM(F8>0,F8>24,F8>25,F8>32),$V9:$V30,$AB9:$AB30,$AH9:$AH30,$AN9:$AN30),1),

COLUMN(B1),SUM(F8>0,F8>24,F8>25,F8>32))

Cheers!

Twifoo

Best Response confirmed by
Alcesterman (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-09-2019 12:08 PM

I did implement a solution but I had overlooked the fact that your longest suit length etc. changed as the vulnerability or contract suit changed across the columns.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 10:31 AM

Twifoo is a star!

The formula works - not sure how - but it works! (Now my intellectual challenge is to see how it works.)

Many thanks (and a game of bridge if you come near Alcester in the UK!)

Many thanks to all who advised.

Alcesterman

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 10:34 AM

@Twifoo is a star!

The formula works - not sure how - but it works! (Now my intellectual challenge is to see how it works.)

Many thanks (and a game of bridge if you come near Alcester in the UK!)

Many thanks to all who advised.

Alcesterman

The formula works - not sure how - but it works! (Now my intellectual challenge is to see how it works.)

Many thanks (and a game of bridge if you come near Alcester in the UK!)

Many thanks to all who advised.

Alcesterman

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 02:09 PM

The formula uses the reference form of INDEX. CHOOSE returns the lookup_array argument of MATCH. The first SUM returns the index_num argument of CHOOSE. The second SUM returns the area_num argument of INDEX.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019 09:37 AM

Many thanks. @Twifoo gave me a solution

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019 09:39 AM

Many thanks. @Twifoo gave me the solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019 09:41 AM

Q.E.D.

(as I was taught at school 70 years ago!)

Many thanks

(as I was taught at school 70 years ago!)

Many thanks

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
24.5K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
23.2K
Views

10 Likes

13 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
135K
Views

6 Likes

28 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11K
Views

0 Likes

9 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
22.7K
Views

0 Likes

7 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft