# Lookup help, multiple columns

#### texasduckhunter

##### New Member
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
BD20 has lead time
BE20 has pricing
BF20 has lead time
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 [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=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
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=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

</strike>
[/FONT]

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### DanteAmor

##### Well-known Member
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))

#### texasduckhunter

##### New Member
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

#### DanteAmor

##### Well-known Member
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.

#### texasduckhunter

##### New Member
That seems to have done the trick!

Thank you very much!

#### DanteAmor

##### Well-known Member
That seems to have done the trick!

Thank you very much!

#### texasduckhunter

##### New Member
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
20 has lead time
CR
20 has pricing
CS
20 has lead time
and so on

i need the same kind of help please.

#### DanteAmor

##### Well-known Member
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.

#### texasduckhunter

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

#### DanteAmor

##### Well-known Member
there are some cells with no data, how does it skip those?
Example:

2 Blank 3

What's the result? 2 or blank?

1,101,819
Messages
5,483,087
Members
407,379
Latest member
Bender1964

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...