# Thread: Lookup help, multiple columns Thanks: 0 Likes: 0

1. ## Lookup help, multiple columns

I'm unsure what formula I need to return an adjacent value to the min value from a set of columns...

My excel knowledge is very limited

I have data in cells BC20 to CJ20 and down to BC136 and over to CJ136

I have numbers in every other column and text in between

example:
BC20 has pricing
BE20 has pricing
and so on

I have a formula in CK20 to pull min value, im sure this is probably not the easiest way but its all I know =MIN(BC20,BE20,BG20,BI20,BK20,BM20,BO20,BQ20,BS20,BU20,BW20,BY20,CA20,CC20,CE20,CG20,CI20)

In column CL i pull in the vendor information from the column titles
=INDEX(\$BC\$19:\$CB\$19,MATCH(CK20,BC20:CB20,))

Those seem to work great

But my issue is that i need the corresponding lead time to pull into CM from the Min value i have in CK

2. ## Re: Lookup help, multiple columns

Try this

In the CK20 cell you can put the array formula

=MIN(IF(ISODD(COLUMN(BC20:CJ20)),BC20:CJ20))

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

--------------
in CM20:

=INDEX(BD20:CJ20,MATCH(CK20,BC20:CI20,0))

3. ## Re: Lookup help, multiple columns

when i do that it puts nothing in CK and in CM it pulls my min,

i need to note that some columns have no values because theres no price from vendor

4. ## Re: Lookup help, multiple columns

Originally Posted by texasduckhunter
when i do that it puts nothing in CK and in CM it pulls my min,

i need to note that some columns have no values because theres no price from vendor

Try this en CK20

=MIN(IF(ISODD(COLUMN(BC20:CJ20)),IF(BC20:CJ20<>"",BC20:CJ20)))

Is an array formula to accept must be press Shift + Control + Enter.

5. ## Re: Lookup help, multiple columns

That seems to have done the trick!

Thank you very much!

6. ## Re: Lookup help, multiple columns

Originally Posted by texasduckhunter
That seems to have done the trick!

Thank you very much!

7. ## Re: Lookup help, multiple columns

DanteAmor,

you did so well the last time, i tried copying what you did with the following adjustments and it didnt work:

I have data in cells CP20 to DW20 and down to CP135 and over to DW135

I have numbers in every other column and text in between

example:
CP
20 has pricing

CQ

CR
20 has pricing

CS

and so on

i need the same kind of help please.

8. ## Re: Lookup help, multiple columns

Originally Posted by texasduckhunter
DanteAmor,

I have data in cells CP20 to DW20 and down to CP135 and over to DW135

=MIN(IF(ISODD(COLUMN(CP20:DW20)),IF(CP20:DW20<>"",CP20:DW20)))

Is an array formula to accept must be press Shift + Control + Enter.

9. ## Re: Lookup help, multiple columns

there are some cells with no data, how does it skip those?

10. ## Re: Lookup help, multiple columns

Originally Posted by texasduckhunter
there are some cells with no data, how does it skip those?
Example:

2 Blank 3

What's the result? 2 or blank?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•